VBA code to set row height, object required error

[Joyner]

Hello, I haven’t been able to find code that fits my particular situation. Thanks for any help.

I am trying to set my row height and I have put together the below code. I am not a vba expert by a long shot and put this together from other code I was able to find, and I am not sure it will work after I get past the “object required" error.

I am trying to set my row height after text is typed into a cell (using change event, and only on the rows identified by the selection case). First, I do not want the row height to be less than it is currently set, so I do not want to use autofit, and I have some merged cells in the row, so I guess there are problems with that anyways.

So I have calculated a row height in a cell (column “AA”) based on what gets typed into (the length of the characters) in the target cell (column “M” / 13). So I want to compare column M and adjust the row larger if the cell requires it, but not if the row in already large enough (so no adjustment, therefore the reason for getting the current row height)

I have to apologize for not putting my code in properly, my HTML maker wouldn't load for me. Hopefully viewing this short code is okay. This is the code I have, that I have taken from various code I was able to find. If bugs on the CurrentRowHeight = .RowHeight error 424 Object required. Any ideas why I am getting this error and if the rest of my code will do what I want, or am I why off base?

Thanks

If Selection.Column = 13 Then
' Select Case Selection.Row
Case 5, 7, 9, 11, 13, 15, 17, 19, 21, 23, 26, 28, 30

With Selection.Row
CurrentRowHeight = .RowHeight
PossNewRowHeight = Range(Selection.Row, "AA").Value
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End With

End Select

End If

 

[p45cal]

Instead of
With Selection.Row
try one of:
With Selection
With Selection.rows(1).entirerow
With Selection.entirerow

only a guess.

 

[Joyner]

Thanks for the quick reply P45Cal

That got me past the object required error, now I am getting an error on the next line:

PossNewRowHeight = Range(Selection.Row, "AA").Value

run time error 1004 Method Rang of Object_Worksheet 'failed

I am guessing that my method to grab my cell at column "AA" is wrong, and it shouldnt be Selection row either. Any idea what the proper why would be to identify my cell in column "AA" in the selection row? Or could it be my .value?

Thanks again

 

[p45cal]

Range(Selection.Row, "AA").Value
should probably be
Cells(Selection.Row, "AA").Value
or
Range("AA" & Selection.row).value

 

[Joyner]

Thanks again, P45Cal, after playing with it a bit, below is what I came up with that works (after a little testing appears to work as designed). I had a bit of a problem for awhile because I forgot to turn off protect so kept getting errors andI didn't know why and then figured out it was the protect. Turns out, I didn't nned to use "with selection"

If Target.Column = 13 Then

Dim CurrentRowHeight As Single
Dim PossNewRowHeight As Single
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False

Select Case Selection.Row
Case 5, 7, 9, 11, 13, 15, 17, 19, 21, 23,
ActiveSheet.Unprotect
CurrentRowHeight = Target.RowHeight
PossNewRowHeight = Range("AA" & Target.Row).Value

Target.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)

End Select

End If

Thanks again