Improper subtotals using macro recorder

[ExcelPupper]

Hi. I have here a macro for making subtotals for a set of data. However when I run the code, it does not give the proper subtotals.

See below the result when I run the code:

⬇

I also attached the code for reference:

Sub SubtotalMacro()
'
Dim Lastrow As Long

Sheets(1).Activate
Lastrow = Range("A" & Rows.Count).End(xlUp).Row

Range("A1:D" & Lastrow).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3, 4), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
End Sub

 

[Peter_SSs]

I believe that your problem lies in the use of 'Selection' (which the macro recorder gives you but in general is a bad idea as it is mostly not necessary and slows your code)

Your code initially selects A1:D8
However once the first subtotal is done, the data now occupies more than 8 rows but you still only have 8 rows selected.
So when the second subtotal is applied so it only applies to 8 rows.

Try this instead

Sub SubtotalMacro_v2()
  Sheets(1).Activate
  Range("A1:D" & Range("A" & Rows.Count).End(xlUp).Row).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4), Replace:=False, PageBreaks:=False, SummaryBelowData:=True
  Range("A1:D" & Range("A" & Rows.Count).End(xlUp).Row).Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3, 4), Replace:=False, PageBreaks:=False, SummaryBelowData:=True
End Sub

 

[ExcelPupper]

Does it mean that the variable "Lastrow" will ALWAYS have a value of 8 throughout the code? (It will not adjust whether I add or delete rows of data on Column A?)

 

[Peter_SSs]

Does it mean that the variable "Lastrow" will ALWAYS have a value of 8 throughout the code?

Yes, unless you recalculate it along the way like this (though I am not recommending this approach)

Sub SubtotalMacro_v3()
  Dim Lastrow As Long
  
  Sheets(1).Activate
  
  Lastrow = Range("A" & Rows.Count).End(xlUp).Row
  Range("A1:D" & Lastrow).Select
  Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4), Replace:=False, PageBreaks:=False, SummaryBelowData:=True
  
  Lastrow = Range("A" & Rows.Count).End(xlUp).Row
  Range("A1:D" & Lastrow).Select
  Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3, 4), Replace:=False, PageBreaks:=False, SummaryBelowData:=True
End Sub

 

[ExcelPupper]

Yes, unless you recalculate it along the way like this (though I am not recommending this approach)

Sub SubtotalMacro_v3()
  Dim Lastrow As Long
 
  Sheets(1).Activate
 
  Lastrow = Range("A" & Rows.Count).End(xlUp).Row
  Range("A1:D" & Lastrow).Select
  Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4), Replace:=False, PageBreaks:=False, SummaryBelowData:=True
 
  Lastrow = Range("A" & Rows.Count).End(xlUp).Row
  Range("A1:D" & Lastrow).Select
  Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3, 4), Replace:=False, PageBreaks:=False, SummaryBelowData:=True
End Sub

Ohh okay. The one you initially provided is much better than this lol anyway thanks for explaining. Have a nice day!

 

[Peter_SSs]

You're welcome.