Set Minimum Row Height for Used Rows

Question

I have the following code that I can't seem to get to work properly.

If i remove the lastquoterow and change it to be A13:A250 then it works, but it slows the code way down as it has to go through that entire range and check and see if it needs resized (which since no data exist further down yet, means the entire range will be resized). This also causes some optic issues as it causes rows that are inserted later in subsequent code to have wider spacing than is needed.

I also can't have it resize A1-A12, as this my header range that must remain static.I am trying to tell it to start at A13 where my range begins, go to the last used row, if the row size is < 21, set height to 21. Then my next stage of code begins and inserts more data that deals with merging cells and such, which has its own code for setting its row height and can't be messed with.

What am I messing up with this one?

    Dim lastquoterow
        
        lastquoterow = quote1.Columns("A").Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
        
    For Each c In Range("A13" & lastquoterow)
        If c.RowHeight < 21 Then
            c.RowHeight = 21
        End If
    Next

I’m not in front of my computer, but this `Range("A13" & lastquoterow)` could be changed to this `Range("A13:A" & lastquoterow)` . There are other stuff that should be adjusted in your code — Ricardo Diaz, Aug 18 '20 at 12:39
@BigBen adding that second ```A``` did the trick. Some times it's the simple things that mess you up. — Tmyers, Aug 18 '20 at 12:44
@Tmyers - if you only do this - `Range("A13:A" & lastquoterow)`, then most probably one day the code will explode with error **1004**. To save yourself the trouble read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba or use `quote1.Range("A13:A" & lastquoterow)` — Vityata, Aug 18 '20 at 12:59
@Vityata That makes sense with it just going nuclear one day and bugging out. I tried your second method, and it works and is much faster than the previous. I will always take speed, as the rest of my code is horribly inefficient and slow. Hopefully can speed it up one day as I learn. Thank you for your insight and help! — Tmyers, Aug 18 '20 at 13:08

score 1 · Accepted Answer · answered Aug 18 '20 at 12:42

You have to decide what lastquoterow is. If it is a row, then it should be a Long. Although, the .Row at the end states it should be casted to Long.

Anyway, this works for me. 50 is hardcoded for a reason, as far as I do not have your input:

Sub TestMe()

    Dim lastquoterow As Long
    Dim c As Range
    
    lastquoterow = 50
    
    For Each c In quote1.Range("A13:A" & lastquoterow)
        If c.RowHeight < 21 Then
            c.RowHeight = 21
        End If
    Next

End Sub

If it is too slow, you may add the rows to a range and perform the rowsToIncrease.RowHeight = 21 only once, taking a bit less than 2 seconds for 1M+ rows, which should be considered fast:

Sub TestMe()

    Dim lastQuoteRow As Long: lastQuoteRow = 2 ^ 20
    Dim c As Range
    Dim rowsToIncrease As Range
    
    For Each c In quote1.Range("A13:A" & lastQuoteRow)
        If c.RowHeight < 21 Then
            If rowsToIncrease Is Nothing Then
                Set rowsToIncrease = c
            Else
                Set rowsToIncrease = Union(c, rowsToIncrease)
            End If
        End If
    Next
    
    If Not rowsToIncrease Is Nothing Then
        Debug.Print rowsToIncrease.Address
        rowsToIncrease.RowHeight = 21
    End If
    
End Sub

Set Minimum Row Height for Used Rows

1 Answers1