Insert a Blank Row at every other row where active cell <> X or <> Y etc

[stimpsond1]

Hi all,

Firstly, I totally disagree with having to do this but due to politics I have no choice 

I need to insert a blank row in-between every other row of data, however, not if the active cell in column A contains “Dean” or “Bob” or “Fred” etc.
Is this possible?

Today there might be 20 rows tomorrow there might be 50 so I only want to loop the number of times for the data in column A

Thanks in advance

[AB33]

I hope you are not related in any way to the Chancellor of the exchequer.
Give this a try

Sub InsertRows()
Dim LR As Long, i As Long

    Application.ScreenUpdating = False
    LR = Range("A" & Rows.Count).End(xlUp).Row
    
    For i = LR To 1 Step -2
        If InStr(Cells(i, "A"), "Dean") = 0 Or InStr(Cells(i, "A"), "Bob") = 0 Or InStr(Cells(i, "A"), "Fred") = 0 Then
        Rows(i).Insert Shift:=xlDown
        End If
    Next i
    
    Application.ScreenUpdating = True

End Sub

[stimpsond1]

Thanks for such a quick responce, and no, I'm not realted to that......

It is sort of working, it is inserting a line inbetween every 2 rows

[AB33]

If you want in every row, change this line

For i = LR To 1 Step -2

INTO

For i = LR To 1 Step -1

[stimpsond1]

Hi,

I have done this and noticed that it is ignoring the line where <> Bob <> Dean <> Fred

just inserting aline inbetween all.

Column A
Bob
Bob
Bob
Bob
Bob
Bob
Danny
Danny
Danny
Dave
Dave
Dave
Dean
Dean
Dean
Dean
Dean
Dean
Fred
Fred
Fred
Fred
Fred
Fred
James
James
James
James
James
James
Mary
Mary
Mary

[AB33]

I have attached you data in excel. There are two sheets, Ori sheet before running the code and the second after. Could you indicate with a sample what the result would be, so that I can adjust my code

[stimpsond1]

Hi,

Its neither, if the name is Bob, or Fred, or Dean I do not want any spaces inserted, if it isnt any of them then i want a space inserted, makes sense?

i.e. on the ORI sheet A7 - A12 would have a spaces in-between but A13-A18 wouldnt

[AB33]

I have done a sample book for you. All you have to do is to just show me your expected result. Otherwise, it is going to be guess work

[stimpsond1]

Hi,

I cant upload documents due to my companies internet policy but i have amended in excel and then posted in here so hopefully when you paste into a worksheet it will stay with the spaces:

Bob
Bob
Bob
Bob
Bob
Bob

Danny

Danny

Danny

Dave

Dave

Dave

Dean
Dean
Dean
Dean
Dean
Dean
Fred
Fred
Fred
Fred
Fred
Fred

James

James

James

James

James

James

Mary

Mary

Mary

Thanks

[AB33]

Try this one

Sub InsertRows()
Dim LR As Long, i As Long

    Application.ScreenUpdating = False
    LR = Range("A" & Rows.Count).End(xlUp).Row
    
    For i = LR To 1 Step -1
        If InStr(Cells(i, "A"), "Dean") = 0 And InStr(Cells(i, "A"), "Bob") = 0 And InStr(Cells(i, "A"), "Fred") = 0 Then
        Rows(i).Insert Shift:=xlDown
        End If
    Next i
    
    Application.ScreenUpdating = True

End Sub

[stimpsond1]

This is perfect, thank you so much. Thanks for taking the time to help.

regards,

Dean

[AB33]

Dean,
You are welcome!
Could you please now close this thread as solved? Go in to the top right hand of this page, choose "Thread Tools" from the menu, then select "Solved" from the drop down menu.