Insert a blank row after each subtotal line

I have subtotaled my data. Now I need to insert a blank row in between each "total" line. Please help. Otherwise, it is very manual.
Answer
Answer

Hi,

I used a very simplistic data layout to develop this and after running the code below the data look like

Name Sum
Grand Total 119
a Total 9
b Total 18
c Total 27
d Total 23
e Total 13

I can't show the bars to the left to expand/collapse the subtotals but is this what your looking for? hers the code to achieve it if that's what you want. It assume the text are in column A

Sub insert_row()
Dim LastRow As Long
Dim MyRange As Range
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
For x = LastRow To 2 Step -1
    If Right(Cells(x, 1), 5) = "Total" And Cells(x, 1) <> "Grand Total" Then
        Rows(x + 1).EntireRow.Insert
    End If
Next
End Sub


If this post answers your question, please mark it as the Answer.
Mike H
If this response answers your question then please mark as answer.

Mike H

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer

Suppose SUBTOTAL formulas are in column A


Sub InsertBlankRowAfterEachSubtotalLine()
   Dim z As Long

   For z = Cells(Cells.Rows.Count, "A").End(xlUp).Row To 2 Step -1

      If Cells(z, "A").HasFormula And InStr(Cells(z, "A").Formula, "SUBTOTAL("Then
         Rows(z + 1).Insert
         Rows(z + 1).Clear
      End If
   Next
End Sub

Peter

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated December 17, 2020 Views 5,884 Applies to: