G Scott
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:
And turn it into 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
And turn it into 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
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
position this macro in the tblQueryByMillions vba section and run it
Sub locate_grand_total()
Range("B" & UsedRange.Rows.Count).Select
End Sub
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
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:
For 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:
For 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
ASKER
akoster - yours errors on the very first line saying 'object required'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Both of these worked. Thanks to you both.