Code to select header, subtotal, and grand total rows

[woody3737]

For formatting purposes, I'd like to have a macro that will find and select the header, subtotal, and grand total rows in a table of everchanging size and structure. What I can't figure out is how to select just the part of the row that contains these, and not the entire row. For example, if my header and totals range from column A to T in one table but B to AK in another, how are only those cells selected instead of the entire row. Thanks in advance for any help.

 

[woody3737]

Thanks so much Andrew. It worked perfectly. I had a quick question. Do these 2 bits of code do the exact same thing, or are there some situations where one should be used over the other? Thanks again.

 

[woody3737]

Does anyone have any ideas about step 2 from my next to last post? I'm not sure how to code the search of the column and if it finds "Total" how to actually select the cell. I can use Andrew's code to select the row, but what would I change the range to, since it could be anywhere in the column? How can I set it to something like "selected cell"?

 

[woody3737]

In case anyone wanted to use some code similar to this, here is the solution, thanks to xld from VBAX.

Sub FormatHeaderChunk()
    Dim rng As Range
    Dim cell As Range
    Dim FirstAddress As String
    Dim LastRow As Long
    Dim LastCol As Long
     
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    LastCol = Cells(3, Columns.Count).End(xlToLeft).Column
     
    Call FormatCells(Range("A3"), LastCol)
    With Columns("A:C")
         
        Set cell = .Find("Total", Lookat:=xlPart)
        If Not cell Is Nothing Then
            FirstAddress = cell.Address
            Do
                Call FormatCells(cell, LastCol)
                Set cell = .FindNext(cell)
            Loop While Not cell Is Nothing And cell.Address <> FirstAddress
        End If
    End With
    Range("A3").Resize(LastRow - 2, LastCol).BorderAround LineStyle:=xlContinuous, ColorIndex:=xlColorIndexAutomatic
End Sub
 
Private Function FormatCells(rng As Range, NumCols As Long)
     
    With rng.Resize(, NumCols - rng.Column + 1)
         
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .Weight = xlThin
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .Weight = xlThin
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .Weight = xlThin
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .Weight = xlThin
        End With
        .Borders(xlInsideHorizontal).LineStyle = xlNone
        With .Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
        End With
        .Font.Bold = True
    End With
End Function