# Insert Row VBA

#### rusted314

##### Board Regular
I have report that I am adding subtotals to using the Excel 2010 subtotal feature. I now need to add a row before and after each subtotal which is column E in my document. Below is an example. Does anyone have a simple macro that will do this?

 100 111 2/8/2013 100 Total 105 100 2/28/2013 105 313 2/7/2013

<TBODY>
</TBODY>

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
If you do that you'll lose the Sub-Total functionality unless you remove those rows.

It allows me to manually add a row before and a row after?

What I mean is that if you add rows like that then the sheet will be seen as a series of different data ranges, and no longer contiguous. The Sub-Total function needs a contiguous data range.

With a sample Table like so...

Excel 2010
ABCD
1DeptCode Revenue Select
2701895 52.00 y
3701756 75.00 n
4701325 82.00 n
5701111 - n
6702222 15.00 n
770 Total 224.00
8711895 16.00 y
9711777 17.00 n
10711756 18.00 y
11711325 19.00 n
12711111 20.00 n
13712222 21.00 y
14711998 22.00 n
1571 Total 133.00
161001895 23.00 y
171001777 24.00 y
181001756 25.00 n
191001325 26.00 n
201001111 27.00 y
211002222 28.00 n
221001998 29.00 y
23100 Total 182.00
24Grand Total 539.00

</tbody>
Sheet1

Run this code against...

Code:
``````Sub Foo()
With Application
.EnableEvents = False
.Calculation = xlCalculationManual
End With
LR = Range("A" & Rows.Count).End(xlUp).Row - 1
For i = LR To 1 Step -1
If InStr(1, Cells(i, 1), "Total", vbTextCompare) Then
Cells(i, 1).Offset(1).EntireRow.Insert
Cells(i, 1).EntireRow.Insert
End If
Next i
With Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub``````

Unfortunately the VBA didnt work but I appreciate you sharing it with me. Yes, my table is setup very similiar to yours example except my totals are in column E and I am sub totaling columns H and I. To Smittys point maybe this cant be done using the subtotal feature or maybe i need a macro to do the subtotals for me?

I ended up finding a macro online that I was able to modify that did the trick. Thank you all for your help.

Sub InsertRows()
Dim r As Long
Dim m As Long
Application.ScreenUpdating = False
m = Range("E" & Rows.Count).End(xlUp).Row
For r = m To 1 Step -1
If Range("E" & r) Like "*Total" Then
Range("E" & (r + 1)).EntireRow.Insert

End If

If Range("E" & r) Like "*Total" Then
Range("E" & r).EntireRow.Insert

End If

Next r
Application.ScreenUpdating = True
End Sub

Replies
0
Views
588
Replies
1
Views
328
Replies
0
Views
270
Replies
5
Views
633
Replies
0
Views
214

1,196,057
Messages
6,013,176
Members
441,751
Latest member
336448

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back