Loop and insert row VBA

[Excelnoobisme]

Hi,

Assuming on column A i have a set of numbers in ascending order.

i.e.
1
1
2
3
3
3
4
4

how can i run a loop and insert 2 row in between all the different number?

 

[JackDanIce]

Try:

Sub InsertRow()
Application.ScreenUpdating = False
Dim i As Long
i = 1
Do While Not IsEmpty(Range("A" & i))
    If Range("A" & i + 1) <> Range("A" & i) Then
        Rows(i + 1).Insert
        Rows(i + 1).Insert
        i = i + 2
    End If
    i = i + 1
Loop
Application.ScreenUpdating = True
End Sub

 

[Tinus Bergakker]

So you want a macro that performs this?

i.e.
1
1


2


3
3
3


4
4

?

 

[VoG]

Hi. Try

Sub InsRow()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If Range("A" & i).Value <> Range("A" & i - 1).Value Then Rows(i).Resize(2).Insert
Next i
End Sub

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG07Mar39
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Range("A" & rows.Count).End(xlUp).row
[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 <> .Offset(-1).value [COLOR="Navy"]Then[/COLOR]
            .EntireRow.Resize(2).Insert
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Excelnoobisme]

all works perfectly. Thanks alot guys

 

[Excelnoobisme]

Run into some problem as my 1st Row is header. if i run, it will insert rows inbetween the header and 1st number which i dont want to. How can i avoid that?

 

[MickG]

Try:-

For n = Lst To 3 Step -1

 

[JackDanIce]

With the code I suggested earlier, change to:

Sub InsertRow()
Application.ScreenUpdating = False
Dim i As Long
i = 2
Do While Not IsEmpty(Range("A" & i))
    If Range("A" & i + 1) <> Range("A" & i) Then
        Rows(i + 1).Resize(2).Insert
        i = i + 2
    End If
    i = i + 1
Loop
Application.ScreenUpdating = True
End Sub

Change the number 2 to whatever row your data starts on (i.e. not 1 if 1 is the header row)

 

[Excelnoobisme]

Thanks alot once again