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:
 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
LVL 1
G ScottAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
Code:
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    With Range("B13").CurrentRegion.SpecialCells(xlCellTypeVisible)
      With .Borders(xlEdgeTop)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
      End With
      With .Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
      End With
   End With

Open in new window

0
 
Rory ArchibaldCommented:
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. :)
0
 
Arno KosterCommented:
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
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Arno KosterCommented:
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

0
 
G ScottAuthor 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

0
 
G ScottAuthor Commented:
akoster - yours errors on the very first line saying 'object required'
0
 
Arno KosterConnect With a Mentor Commented:
that should be because you have placed the macro in the wrong vba code section.
when you have placed the macro in the "ThisWorkbook" section, excel will not know what to do with range.

so you can either change the macro to

Sub format_totals()

For pos = activesheet.UsedRange.Rows.Count To 13 Step -1
    If InStr(activesheet.Range("B" & pos), "Total") > 0 Then
        With activesheet.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


or move the macro to the "Sheet1" section
0
 
G ScottAuthor Commented:
Both of these worked. Thanks to you both.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.