0

I have a piece of code that is supposed to format a tab of information. I took it from a piece of code previously used and I am modifying it to fit my needs. I am getting a syntax error on the .Selection Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(8, 10), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True line.

VBA isn't exactly a strong point of mine so I would like to figure out a way to perform this properly. I know selection is frowned upon so if anyone has a way around it without me having to redo a bunch of code that would be awesome. 

With ActiveSheet
.Range("A10").Select
.Range(Selection, Selection.End(xlToRight)).Select
.Range(Selection, Selection.End(xlDown)).Select
.Selection Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(8, 10), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
.Outline.ShowLevels RowLevels:=2

.Range("C8").Select
End With
last10seconds
  • 333
  • 1
  • 3
  • 18
  • Doing this by head (and from ancient memory). But that "subtotal" seems really out of place? Shouldn't there be at least a comma behind it? – therebelcoder May 06 '15 at 18:14
  • Further reading on avoiding select: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros – Sobigen May 06 '15 at 18:56

1 Answers1

1

Working code should be:

With ActiveSheet
.Range("A10").Select
.Range(Selection, Selection.End(xlToRight)).Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(8, 10), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
.Outline.ShowLevels RowLevels:=2

.Range("C8").Select
End With

Subtotal is a sub that needs to be called on the Selection. Therefore it needs a period between the two to make the call.

Also Selection is not a property of the ActiveSheet so the preceding period should be dropped inside the With block.

Byron Wall
  • 3,970
  • 2
  • 13
  • 29
  • When doing that I receive a `object doesn't support this property or method` error, and still highlights the same line of code the syntax error popped up at. – last10seconds May 06 '15 at 18:22
  • I also missed that `Selection` should not have a `.` in front of it. It is not a property of the `ActiveSheet` so it belongs outside the scope of the `With`. See edit. – Byron Wall May 06 '15 at 18:34