Macro for row height/last row

[chookers]

Hi, I'm trying to get a macro that will select my rows, beginning at R5, and set the RowHeight at 90 for R5 through whatever the last row with entries is (this will change fairly regularly - one day it'll be 8 rows, the next day maybe 17 rows)

I know how to select the row height, but don't know how it should be combined with more code. This is just going to be used for when the info is printed out to make it more reader friendly.

Any help much appreciated! Thank you!

[zacemmel]

Is the data contiguous?

[chookers]

Yes!!

[zacemmel]

Sub row()
xrow = 5
Do While Len(Cells(xrow, 18).Value) > 0
xrow = xrow + 1
Loop
Rows("5:" & xrow).RowHeight = 80
End Sub


This should work if the data is contiguous.

[zacemmel]

Oops, make that 90 instead of 80

[Colo]

Hi. I guess it will be easy to use, useing Worksheet_Change event.

Right click on the sheet tab, select View Code and Please copy this into there.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

  Range([A5], [A65536].End(xlUp)).EntireRow.RowHeight = 90

End Sub

[chookers]

Zasemmel - tried your code, it works BUT it only makes R5 RowHeight=90, not all the rows with entries(they definitely are contiguous)-any tweaking that may help??

Thank you!

Will try the other suggestion and see what happens

[zacemmel]

It should work for that whole range assuming there is data in R6 etc. I tested the code myself and it worked fine assuming the cells are contiguous.

[chookers]

Ok Colo did as instructed - pasted the code but how do I use it now? Do I have to name it and assign it to a macro? Haven't used private sub before, am a novice for sure!

Thanks!

[chookers]

Zacemmel (Jeez did I spell that right?) Yes Just my luck it would work for you and not me! Rats! Am pasting it below, I've got info in R6 - R11 on this particular sheet and it's still only doing it for R6 (I meant to say R6 earlier, not R5 - sorry1)

Well if this is what's working for you it must be my worksheet? Thanks for your help anyway! If anyone else sees a solution...help!

Cheers!
Dim xrow As String
xrow = 6
Do While Len(Cells(xrow, 18).Value) > 0
xrow = xrow + 1
Loop
Rows("6:" & xrow).RowHeight = 90