We help IT Professionals succeed at work.

Subtotal Row Selection

G Scott
G Scott asked
on
265 Views
Last Modified: 2013-11-05
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:
 From this
And turn it into this:

 To This
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
Comment
Watch Question

Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
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

Author

Commented:
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:

 ResultFor 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

Author

Commented:
akoster - yours errors on the very first line saying 'object required'
Grand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Both of these worked. Thanks to you both.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.