Macro for row height/last row

chookers

Board Regular
Joined
Mar 16, 2002
Messages
115
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!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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.
 
Upvote 0
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.

<pre>
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Range([A5], [A65536].End(xlUp)).EntireRow.RowHeight = 90
End Sub
</pre>
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top