Insert Row Between Sets of Data

[Estarriol]

Hi Guys,
First of all, apologies if this question has already been answered, I've tried searching but haven't been able to find anything to help so far.

I want to be able to insert a blank row between sets of data, ie:

A1: Customer Name B1: Invoice Date C1: Invoice Amount
A2: ABC B2: 1/9/2011 C2: $10.00
A3: ABC B3: 5/9/2011 C3: $15.00
A4: DEF B4: 3/9/2011 C4: $20.00
A5: GHI B5: 7/9/2011 C5: $10.00

Insert blank row between customer ABC and customer DEF then another between DEF and GHI.
continuing down through more customers until there are no more rows with data

Result:

A1: Customer Name B1: Invoice Date C1: Invoice Amount
A2: ABC B2: 1/9/2011 C2: $10.00
A3: ABC B3: 5/9/2011 C3: $15.00
A4: blank B4: blank C4: blank
A5: DEF B5: 3/9/2011 C5: $20.00
A6: blank B6: blank C6: blank
A7: GHI B7: 7/9/2011 C7: $10.00


Thanks in advance,

Al

 

[MickG]

Try this:-

Dim n As Long
Dim Lst As Long
Lst = Range("A" & Rows.Count).End(xlUp).Row
For n = Lst To 2 Step -1
    With Range("A" & n)
        If Not Cells(n, 1).Offset(-1) = Cells(n, 1) Then
            Cells(n, 1).EntireRow.Insert
        End If
 End With
Next n

Mick

 

[Estarriol]

Hi Mick,
Many thanks,
works perfectly.
Now, is there any way to stop the user pressing the macro key twice.....
and having 3 lines between each data set.....sigh.........

So, is there a way to check if there are already blank lines between the data sets then not running? if not, I'll just reinforce the command with the user.... DON'T RUN THE MACRO TWICE!!!!!!!

Try this:-

Dim n As Long
Dim Lst As Long
Lst = Range("A" & Rows.Count).End(xlUp).Row
For n = Lst To 2 Step -1
    With Range("A" & n)
        If Not Cells(n, 1).Offset(-1) = Cells(n, 1) Then
            Cells(n, 1).EntireRow.Insert
        End If
 End With
Next n

Mick

 

[MickG]

This should only run once and should cater for cells than have nothing or 1 in column "B".

[COLOR="Navy"]Sub[/COLOR] MG08Sep14
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Static run
Lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]If[/COLOR] run = False [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]For[/COLOR] n = Lst To 2 [COLOR="Navy"]Step[/COLOR] -1
    [COLOR="Navy"]With[/COLOR] Range("A" & n)
     [COLOR="Navy"]If[/COLOR] .Value <> "" And .Offset(, 1) > 1 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not Cells(n, 1).Offset(-1) = Cells(n, 1) [COLOR="Navy"]Then[/COLOR]
            Cells(n, 1).EntireRow.Insert
            run = True
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Estarriol]

Thanks Mick, You're a genius!!!!

This should only run once and should cater for cells than have nothing or 1 in column "B".

[COLOR="Navy"]Sub[/COLOR] MG08Sep14
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Static run
Lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]If[/COLOR] run = False [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]For[/COLOR] n = Lst To 2 [COLOR="Navy"]Step[/COLOR] -1
    [COLOR="Navy"]With[/COLOR] Range("A" & n)
     [COLOR="Navy"]If[/COLOR] .Value <> "" And .Offset(, 1) > 1 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not Cells(n, 1).Offset(-1) = Cells(n, 1) [COLOR="Navy"]Then[/COLOR]
            Cells(n, 1).EntireRow.Insert
            run = True
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[macsolvers]

I have a slightly different need:

I want to write a macro that automatically inserts a blank row every third row, starting with row 4 and continuing until the bottom of the database.

R1=header
R2+R3=first group
R4=blank
R5+R6=second group
R7=blank
R8+R9=third group
R10=blank
etc