VBA to set row height not working

[bisel]

Hello All,

Try as I might, I cannot get this macro to execute without an error ...

Private Sub worksheet_activate()

Dim c As Range
Application.ScreenUpdating = False

' Set Row heights

For Each c In Range("rowheights")
[INDENT]If c.Value = 0 Then ' if zero, then set rowheight to autofit[/INDENT]
[INDENT=2]c.EntireRow.AutoFit[/INDENT]
[INDENT]Else[/INDENT]
[INDENT=2]c.EntireRow.RowHeight = c.Value[/INDENT]
[INDENT]End If[/INDENT]
Next c

End Sub

The macro errors when trying to set the row height = c.value

If I use this statement instead ...

c.EntireRow.RowHeight = 50

The row height gets set to 50. But I am unable to set the row height equal to the cell's value.

Anyone know what I am doing wrong?

Thanks,

Steve

 

[akmatz]

Try this:
(highlight the range (selection) first)
(yellow is new/changed text from your sample)

 

[rollis13]

Hi to all.
@bisel , you didn't let us know what error your macro throws and also you didn't state what sort of values you have in range "rowheights", are you sure that all cells contain 'true' numbers ?

 

[bisel]

Hi to all.
@bisel , you didn't let us know what error your macro throws and also you didn't state what sort of values you have in range "rowheights", are you sure that all cells contain 'true' numbers ?

I found my error. One of the cells in the range was formatted as text. After ensuring all the cell values were indeed numeric, the macro works as I would expect.

Thanks,

Steve

 

[rollis13]

If your issue could happen again you can use the CInt or CLng function to convert a string to an integer or long (respectively) data types.
Example, change the line to: c.EntireRow.RowHeight = CInt(c.Value) Anyway thanks for the positive feedback, glad we were able to help.
By the way, you probably need to mark this thread as 'Solved'.