I work with excel files that have cells that are used for entering text of greatly varying lengths. I want to write a macro with a case statement that checks the length of the text - I would write a series of macros that would be called depending on the result of the case statement, something like this:

current.cell check length

case 1 to 99
mcrRowHeight22

case 100 to 199
mcrRowHeight33

case 200 to 299
mcrRowHeight44

the part I'm having trouble with is the LEN function syntax ... if LEN is what is to be used

The structure could be: (I'm using Target, assuming your're putting this code behidn a worksheet, in the worksheet change event - but you can put it somewhere else, and use ActiveCell, instead of Target.

Select Case Len(Target.Value)

case is 1 to 99
Call mcrRowHeight22

case is 100 to 199
Call mcrRowHeight33

case is 200 to 299
call mcrRowHeight44

End Select

This post has been edited by ipisors: Feb 24 2012, 12:20 PM

I would assume something like

all,
that worked great ! Thanks ! (IMG:style_emoticons/default/cool.gif)

now I have to loop the code...

what statement would I make ? In english, "IF CELL value is null/blank, then exit sub" ? What would the syntax be ?

What is it that you're actually looping through? What is the loop?

Based on your earlier code, it's just a matter of select case. If none of the case equal true, then nothing happens.

Can you post teh code you're using so far? without that it's kind of confusing what your'e doing

in the interest of space, here's the macros being called:

Sub mcrRowHeight30()
'
' mcrRowHeight22 Macro
' 2/24/12 - Name says it all - to be used in conjunction with CASE statement
'
ActiveCell.Rows("1:1").EntireRow.Select
mcrFormatAsWrap ' call macro
Selection.RowHeight = 30
ActiveCell.Offset(1, 0).Range("A1").Select

End Sub

Sub mcrRowHeight35()
ActiveCell.Rows("1:1").EntireRow.Select
mcrFormatAsWrap ' call macro
Selection.RowHeight = 35
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Sub mcrRowHeight40()
ActiveCell.Rows("1:1").EntireRow.Select
mcrFormatAsWrap ' call macro
Selection.RowHeight = 40
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

I tried just inserting Case 0 To 1 ------> that did NOT work

This post has been edited by bakersburg9: Feb 24 2012, 12:54 PM

Ok, and which code do you feel needs an Exit sub?

Not sure how you're calling SetRowHeightCustom, but however it is, it evaluates the length of the active cell. If one of those cases is true, it calls that macro. If it's not, nothing happens.

What else is needed

I'm going through a range of cells, say B2:B23, one at a time (Maybe that's not the optimal way) - it works perfect if I run the macro for every cell - manually fire it off, I want it to go by itself, evaluate Cell B2, B3------ automatically, then stop at B24, since the cell is blank

What do you actually want to be the event that triggers this code?

there are a few ways to approach this. You can hard code the range to work with or if you know that the rows will always have data until the end of the column then you can test for an empty cell.
here is some untested aircode and the syntax may be slightly off but the concept is there.

That worked great ! For the most part, all I did was add the do until line and the loop -and we have a winner ! This is a pretty neat little trick if I do say so myself ! Thanks, ALL !!!! (IMG:style_emoticons/default/cool.gif)

I have to disagree a little bit with Bob in this particular case - Never use 'select' or 'active' in your code if you can avoid it. It will work for a while, then not work, off and on, and you won't know why. : )

That's why I asked specifically, what/when do you want this code to fire. Once we know that, we can set up code that explicitly refers to the correct cells.

Lastly - as far as finding the last cell with data, I wouldn't go from the top down, because if you have gaps in data you'll get the wrong place. Go from the bottom up.

Dim ws as Worksheet
Set ws= thisworkbook.worksheets("Sheet1")
LastCell = ws.range("A" & ws.rows.count).end(xlup).row

That's the most reliable way I've found to find the last cell with data (vertically) in a worksheet.

I didnt notice how you move to the next row in your code which was the offset statement I used.. The reason I didnt put it inside what you had provided was so that you can see how you can call it or other functions that you may create separately.

As Isaac points out you will find many different opinions, all valid for the most part on how to approach things. I personally very rarely start at the bottom as being an old finance guy i am used to having subtotal and total lines at a row below my data. so i start at the beginning of the data and work down. I do build in flags if I encounter an empty cell when it shouldnt be there. As for the use of .select, i have had no issues. but, i generally have less than 50 rows of data.

Issac - for THIS project, that's not an issue, but as a rule, I'll take your advice !!!!

Hope my statement about this being a 'pretty neat little trick,' wasn't perceived as arrogant, but I would think that around Excel users all over the world somewhere who are faced with the arduous task of cleaning up a report, and they are quite possibly MANUALLY adjusting row height, setting format=wrap manually, in cases where there are various lengths of text is there some OTHER way people are handling this situation ?

Thanks again !

Steve

No not at all, these threads should contain varying viewpoints and even disagreements, it all makes for better content to learn from each other.

I assume that the standard way of double-clicking the column divider and row divider (which automatically shoots col width and row height), isn't working?

the last point I would like to make is that you should always include error handling in your vba modules.

Sure that works, but it's a manual process - maybe quicker than set row height = whatever, but still manual - plus, it's beholden to whatever Excel sets the height at via the double-click process - what if you wanted it a little higher ?

I came up with the solution I posted - but now I need to look at the highest number between one column and the adjacent column

This works for one column - how would I 'tweek' it to where it evaluates the current cell AND the cell to the right - if the currrent cell has the greatest length, continue running the procedure on that cell - if not (the case where the cell to the right has MORE characters in the text string), then move to the right and run the macro to set the row height there.

Here's the 'old' code - how would I change it ?

how do I make the "target" the active cell, and the cell to the right, and call my sub-routine based on the results of the evaluation


* First evaluate LEN of Cell D2
* Evaluate Cell E2

Scenario A:

# characters is longer in Cell D2 vs. E2

= proceed as before, when the cells in column D were the issue

Scenario B

# characters is longer in Cell E2 vs D2

perform actions on cell in Column E, then go back to column D, and start the process over, comparing D3 to F3