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.

 

[Diablo II]

if you read this right, if you select the first cell click and hold move the mouse to the last cell you wont then let go of button that will just pick the cell you wonted if you wont to pick more rows in defrent rows then after doing the first then go to the next cell hold the ctrl button down and pick both will stay selected.

 

[woody3737]

I was trying to move away from just highlighting the rows one by one with the cursor because a lot of my tables have 20-30 subtotal lines and it gets old having to select all those to apply formatting, especially when about half way through I accidentally select something I shouldn't have, and then have to start all over. I know Excel (2007 BTW) is able to see subtotals, because the grand total sum function references them, and headers, because the filter. I just can't figure out what the code would be.

 

[Diablo II]

formatting borders? of formulas?

 

[woody3737]

It's basically cell formatting. Our reports have to have the cells highlighted in yellow, in bold, and with the thick borders. The header needs to be centered, up top, with wrapped text. I know how to create this code, but I can't figure out the code to automatically select the appropriate cells(headers, sub and grand total rows) before applying these formats.

 

[Diablo II]

ok to pick cells its Range("cell like A1").select or Range("A1:A30").select
and here is some other setting

Range("A1:A30").Select
    Selection.NumberFormat = "General"
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = true
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

 

[woody3737]

Thanks for the continuing help Diablo. I think, though, that you have misunderstood exactly what I am looking for. I know how to actually apply the formatting needed. My problem is that I can't figure out the code to actually select the cells that need the formatting since they are always changing. For example, considering the header, assume it ranges from A3 to S3. I'm trying to figure out the code that will find the range for the header and apply the formatting to only those cells. The tables are never the same so I can't use Range("A3:S3") since the next table's header might be A3:K3. The code needs to specifically select a table's header, subtotals, and grand total when they are never in the same range. Thanks again for the input.

 

[woody3737]

Anyone have any thoughts? Is this something that can be done with VBA?

 

[woody3737]

The problem I'm having is that the cells I need to select are never in the same range. I haven't been able to find any code that can do this, so I think I might just have to take the long way around. The header is formatted one way, while the subtotals and grand total rows are formatting another way. The header always begins in A3. I was thinking of something along the lines of this:

1. Select A3 and extend selection out to the last cell in row 3 that has data in it. With that selected, I can apply my formatting code

2. Search column A for any cell that contains the word "Total", select that cell, and then extend that selection out to the last cell with data. Then I'll apply my formatting code. Next, continue down searching for the next cell in column A that contains "Total" and do the same, all the way down to the last row of data which will be the grand total.

3. Repeat step 2 for column B and C

I already have the code to format these selections, but the other parts of this project are beyond me at the moment. Any thoughts?

 

[Andrew Poulsom]

To get the headers:

Dim Rng As Range
'Choose one of the following
Set Rng = Range("A3").CurrentRegion.Rows(1)
Set Rng = Range("A3").CurrentRegion.Resize(1)