help - I need to auto insert a row

[dollfin003]

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

 

[pgc01]

Hi Dawn

Please try this 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

 

[dollfin003]

this looks great, now how do i use it lol

 

[dollfin003]

OMG It worked perfectly

THANK YOU

 

[pgc01]

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]

I thought from you reply that you needed intructions but you were too fast!

Great, I'm glad it worked!

Cheers
PGC