Link to home
Start Free TrialLog in
Avatar of G Scott
G ScottFlag for United States of America

asked on

Subtotal Row Selection

I have a sheet in which I use the subtotal function. I group in when there is a change in the first column. Is there a way to format the row based on a 'Like' value or anything like that. Here is what I mean. I want to be able to take this:
 User generated image
And turn it into this:

 User generated image
The row count will be different every time. I just want to Top Border the Subtotal rows. I was hoping that you could find a cell in a column with the word 'Total' in it or something. Anyway, any help you can give me on this will be greatly appreciated.

Thanks for taking the time to look. Original.xls
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Collapse the data so only the subtotal rows are showing, select it, press Alt+; to select only the visible cells, then apply the borders you want. Job done. :)
you mean something like


Sub locate_grand_total()
    Range("B" & UsedRange.Rows.Count).Select
End Sub

Open in new window


position this macro in the tblQueryByMillions vba section and run it
or a function to format the rows with totals :

Sub format_totals()

For pos = UsedRange.Rows.Count To 13 Step -1
    If InStr(Range("B" & pos), "Total") > 0 Then
        With Range("B" & pos & ":V" & pos).Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .Weight = xlThin
        End With
    End If
Next pos

End Sub

Open in new window

Avatar of G Scott

ASKER

I forgot to mention one thing, it has to be done with VBA. So, is there a way with a Macro to do this? I did what you said and recorded the steps while I did it. Here is the result though:

 User generated imageFor some reason it didn't grab the last row?!?

I need to be able to select all visible rows that will vary. It could go down to row 200+. Does that make sense? Sorry I forgot to say it had to be macro based, and that it needs to be dynamic so that it can adjust to different row counts.

Kind of like this:

Range("B13:U13").Select 'This range will always be static

'But then I need to to move the selection down to find the last
'VISIBLE row/cells with values.

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Open in new window

Avatar of G Scott

ASKER

akoster - yours errors on the very first line saying 'object required'
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of G Scott

ASKER

Both of these worked. Thanks to you both.