Excel macro: how do I change all row heights, BUT

Question

I'm working on a long list of data (Column B) that has been formatted using bold and indents. The bold cells contain the titles/category names and the indented cell values are the subcategories.

The row heights are all over the place. It should have been 10.5 for everything, and the bold cells/rows 15. I can change everything to 10.5, but then I need to spend quite a bit of time scrolling through the list amending the bold row heights. I've used the format painter but it's a long list and I didn't want to spend so much time on this part of the process. And now I know that I'll need to do this to another 30 documents.

Does anyone have a quicker way of doing this?

Answer 1

Sub setHeights()
Dim targetRange As Range
Dim targetCell As Range

    Set targetRange = Range("B:B")
    For Each targetCell In targetRange
        If Not IsEmpty(targetCell) Then
            If targetCell.Font.Bold Then
                targetCell.RowHeight = 15
            ElseIf targetCell.Font.Superscript Then
                targetCell.RowHeight = 12.75
            Else
                targetCell.RowHeight = 10.5
            End If
        End If
    Next targetCell
End Sub

You might want to change Range("B:B") to something like Table1.Range("B1:B255")

Answer 2

Rather than setting the range as shown above, Set targetRange = Range("B:B")

Try setting it like this: Set TargetRange = Range("B1", Range("B65536").End(xlUp))