VBA: Insert blank row each time left(cell,5)="Total"

squidmark

squidmark

Board Regular
Joined
Aug 1, 2007
Messages
105
I need to look at each cell in column F, and if the last 5 characters in the cell = "Total", then insert a blank row below that cell.

The "Total" is being generated by subtotals. so once those subtotals have run, I need to add a space between each group.

Any help would be greatly appreciated.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
This may not quite work for you but if the actual subtotal formulas were in column G (with no other formulas already in that column) then you could try
Code: 
Sub InsertRows()
    Range("G1", Range("G" & Rows.Count).End(xlUp).Offset(-1)) _
        .SpecialCells(xlCellTypeFormulas).Offset(1).EntireRow.Insert
End Sub

Give it a try in a copy of your workbook (adjusting the column if needed).
 
Upvote 0
Peter_SSs said:
This may not quite work for you but if the actual subtotal formulas were in column G (with no other formulas already in that column) then you could try
Code: 
Sub InsertRows()
    Range("G1", Range("G" & Rows.Count).End(xlUp).Offset(-1)) _
        .SpecialCells(xlCellTypeFormulas).Offset(1).EntireRow.Insert
End Sub

Give it a try in a copy of your workbook (adjusting the column if needed).
Click to expand...

That was perfect. Thank You.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Sumproduct with Table Rows doesn't work
Replies
8
Views
300
Nicha
N
M
  • Question Question
VBA to group rows that are between blank cells
Replies
1
Views
353
mstar0923
M
A
  • Question Question
How can I create a button to insert a new row and update related formulas?
Replies
0
Views
210
Arlochorim
A
S
  • Question Question
Simply current workload. Pivot table?
Replies
0
Views
179
ShadowRider
S
M
  • Solved
vba to sum up each column for each employee on column (A)
Replies
6
Views
330
kvsrinivasamurthy
K

Forum statistics

Threads
1,224,551
Messages
6,179,473
Members
452,915
Latest member
hannnahheileen

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top