help - I need to auto insert a row

dollfin003

New Member
Joined
Aug 27, 2006
Messages
15
Hi all, is it possible to auto insert a row at a blank cell in a large spread sheet. for example

column column
a b

row 1 company a date
row 2 company a date
row 3 company a date
row 4 blank
row 5 company b date
row 6 company b date
row 7 blank

What I need to to is insert a new row at each blank so it looks like this

column column
a b

row 1 company a date
row 2 company a date
row 3 company a date
row 4 blank
row 5 blank
row 6 company b date
row 7 company b date
row 8 blank
row 9 blank

The spread sheet is over 20000 rows large. Any help is appriciated

Dawn
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi Dawn

Please try this code:

Code:
Sub InsertBlankRows()
Dim l As Long, lLastRow As Long

lLastRow = Range("A" & Rows.Count).End(xlUp).Row
For l = lLastRow To 1 Step -1
    If (Cells(l, 1) = "") And (Cells(l, 2) = "") Then _
        Rows(l).Offset(1).Insert
Next
End Sub

Remark: It was not clear in your post what you wanted in case a blank row follows another blank row. I opted for inserting 1 row for each blank row found.

Hope this helps
PGC
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi again

Copy your data to a test worksheet (never test vba or whatever in your source data)

Right-click on the test worksheet tab, choose View Code and you'll be taken to the VB editor.

Insert>Module

If it's the first module you insert, the name is usually Module1.

Paste the code I posted.

Run>Sub/User Form, select InsertBlankRows and click Run.

Check in the worksheet if the result is what you expect.

Hope it's clear
PGC
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
I thought from you reply that you needed intructions but you were too fast!

Great, I'm glad it worked!

Cheers
PGC
 

Forum statistics

Threads
1,137,332
Messages
5,680,867
Members
419,937
Latest member
Talic

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
Top