Setting multiple row heights in VBA

[Extivalis]

Hi All,
I've seen a few different options for setting a row height across and entire sheet/tab in VBA, but I haven't had luck with this (sorry if I'm just missing it):

I want to set all row heights in a specific sheet/tab to 15, unless there is data in Column A, for those rows I need to have the row height be AutoFit. If it makes a difference (and is possible), I'd also like to leave users with the ability to still manually adjust the row height after the fact without having to go in and remove a conditional formatting rule.

Thanks in advance.

 

[John_w]

I don't think you can set row heights with conditional formatting. Try this macro.

Option Explicit

Public Sub Set_Row_Heights()

    Dim rowsRange As Range
    Dim thisRow As Range
    
    With ActiveSheet
        Set rowsRange = .Range(.Cells(.UsedRange.Row, .UsedRange.Column), .Cells(.UsedRange.Row + .UsedRange.Rows.Count - 1, .UsedRange.Column + .UsedRange.Columns.Count - 1))
    End With
    
    Application.ScreenUpdating = False
    
    For Each thisRow In rowsRange.EntireRow
        If IsEmpty(thisRow.Cells(, 1).Value) Then
            thisRow.RowHeight = 15
        Else
            thisRow.AutoFit
        End If
    Next
    
    Application.ScreenUpdating = True
    
End Sub

 

[Extivalis]

If it would be easier: if there's a straight forward way to use VBA to look through the whole tab/sheet and autofit the row height for any row with data in Column A, I could always just record a macro setting all the row heights to 15, then following with the autofit coding for data in Column A.