SubTotal (Exported Data Has Blank Rows)

[spyldbrat]

I need to add subtotals after each grouping in column F. The spreadsheet exports with a blank row between each grouping. I am assuming that the Data/Subtotal feature is not working because of the blank rows. Is there a way I can get the subtotal to show after each grouping in column F? A grand total would be nice, but not needed. The number of rows in each grouping and the amount of locations will vary with each export. I ultimaely plan on using the solution in a macro. I uploaded an image of the spreadsheet

 

[kweaver]

If you please use XL2BB, anyone helping with a possible solution doesn't have to retype data to illustrate it.

 

[spyldbrat]

If you please use XL2BB, anyone helping with a possible solution doesn't have to retype data to illustrate it.

How can/do I use it?

 

[kweaver]

- Posting Data use XL2BB version 2 (Jan 2021)

 

[kweaver]

See if this works:

Sub subtot()
Dim lr As Long, i As Long, st As Double, gt As Double
lr = 2 + Cells(Rows.Count, "F").End(xlUp).Row
i = 8
st = 0
gt = 0
While i < lr
  If Cells(i, "F") <> "" Then
    st = st + Cells(i, "F")
    i = i + 1
  Else
  Cells(i, "F") = st
  Cells(i, "E") = "Sub-Tot"
  gt = gt + st
  st = 0
  i = i + 2
  End If
Wend
Cells(i, "E") = "Grand Total"
Cells(i, "F") = gt
End Sub

 

[spyldbrat]

It works perfect!!!!

Thank you!

 

[kweaver]

Great. Thanks for the confirmation. Please mark it solved so others don't spend time on it unnecessarily unless they find a better method.