• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

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
0
G Scott
Asked:
G Scott
  • 3
  • 3
  • 2
2 Solutions
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now