Format rowheight starting at row x then every nth rows

[Xtrimmer]

I was wondering if there was a good way to format multiple row heigths at once. What I need is, starting at row 4 and then every 9th row thereafter for the whole sheet, format the row height to 37.5. Any ideas? Thanks!

 

[Joe4]

That can be done with a simple little macro. It takes a minute or two to run:

Sub MyRowHeight()
 
    Dim i As Long
    Dim myLastRow As Long
    
    Application.ScreenUpdating = False
    
'   Set initial value
    i = 4
    
'   Find possible number of rows
    myLastRow = Rows.Count
    
'   Do for whole sheet
    Do Until i > myLastRow
        Rows(i).RowHeight = 37.5
        i = i + 9
    Loop
    
    Application.ScreenUpdating = True
 
End Sub

 

[Jonmo1]

Might be slightly faster without a loop...

Sub test()
Dim srow As Long, rwcnt As Long
srow = 4 'Beginning Row
rwcnt = 9 'Interval (every x rows)
 
Rows(srow).RowHeight = 37.5
Range(srow & ":" & srow + rwcnt - 1).Copy
Range(srow + rwcnt & ":" & Rows.Count).PasteSpecial xlPasteFormats
End Sub

Although this copies all formats, not just the row heights.
But given the context, it seems you want that pattern anyway..

 

[Xtrimmer]

Thanks so much to both solutions. I can actually use them both depending on whether I have data & formatting in the sheet already and only need row height or starting a new sheet when the faster all format copying will be just fine!!!

 

[MPW]

jonmo1,

What new doctrine is this? One without loops?
I feel like someone who just saw fire for the first time.

Could you explain how this works?

 

[Jonmo1]

jonmo1,

What new doctrine is this? One without loops?
I feel like someone who just saw fire for the first time.

Could you explain how this works?

If you put some values in a couple cells, Say
A1 = A
A2 = B

Then copy A1:A2
Then highlight A3:A100
Then Paste

You will see it repeats A B A B A B etc... all the way down.


That's all my code does.
main difference is it is using pastespecial - formats
So only the formats are pasted (which includes row heights).

And it looks alot more complicated because it uses variables to determine which row to start in, and how many rows to copy.

But that's about it.

 

[MPW]

I did know that about copy/paste but never thought to apply it like you did. I like simple, it works for me! Sometimes I over think stuff. Thanks for the answer.