Copy/Paste Row Height to Select Rows

[largeselection]

Hi,

I'm hoping there is an easy fix for this. I have a sheet which has a number of subtotal rows (the number of subtotal rows may vary depending on the data). I'd like to increase the row height for only the subtotal rows (not the rows of data included in the subtotal (above the subtotal row).

So I know I can use the Format painter, but I don't want to select all rows. I can also copy/paste formats, but that only works for one row at a time and I may have more than 100 subtotal rows in a sheet. I tried copying one expanded row, then selecting the other subtotal rows and choosing select visible cells only, but then it said the copy/paste were not the same shape.

Any ideas for an easy/quick solution to this?

Thanks!

 

[GopherUK]

You just want all the subtotal rows to be the same height? If so, probably easiest to select them all then drag one to the desired height which will change them all to that height.

 

[largeselection]

Thanks for the suggestion and for looking!

That was actually the first thing I tried, unfortunately, that only works for the one row I actually select to expand. The rows are not clustered together so I selected all of the subtotal rows, then clicked select visible cells only (because I didn't want to have selected the non subtotal rows even though they are collapsed), then tried to expand a row (both holding the ctrl key and not holding it) and only the row that I actually clicked on expanded, the rest remained highlighted, but did not change height.

 

[GopherUK]

Yes, sorry, that wont work if they are not adjacent.

Try selecting all the rows again, right click on one row number>Row Height>adjust as required. That will do all of them.

 

[largeselection]

Thanks again. I also tried that already. Unfortunately as soon as I right click on a row number then only that row remains highlighted and the rest are deselected.

 

[largeselection]

I figured out a solution that seems to work.

So I added a button to the QAT for "Row Height". Then I selected all the rows I wanted, clicked the button to select visible cells, then clicked the row height button, input the size I wanted and they all adjusted as expected.

Thanks!

 

[Perksy_no1]

Hi there,

Dont know if this will help but some one gave me some code on here recently for bolding my sub totals.

Basically it looks for the word total the column you pick the it goes down and bolds the entire row.

I've changed the code to alter the row hieght now.

hope this helps

Sub BoldDataLookingForTotal()
'This Macro lets you pick a column then it looks down the column and Bolds the entire row when It see the word "Total"
    LASTROW = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lastCol = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Set rng = Range(Cells(1, 1), Cells(LASTROW, lastCol))
    Set Var1 = Application.InputBox(Prompt:="Select a single cell to define the Column you want to look for the word Total then Bold that Row", Type:=8)
    On Error Resume Next
    Application.ScreenUpdating = False
    If Var1.Rows.Count <> 1 Or Var1.Columns.Count <> 1 Then
        MsgBox "You did not select a single cell! Bye!"
        Exit Sub
    End If
    On Error GoTo 0
    c = Var1.Column
    For r = 1 To LASTROW
        If InStr(UCase(rng(r, c).Value), "TOTAL") Then Rows(r).RowHeight = 20.00    
Next r
    Application.ScreenUpdating = True
End Sub