Please could someone help me stop my loop

[Scarteg]

Hi

I have been trying to write a loop macro that will delete various rows in my spreadsheet. It is deleting the right rows but i can't get it to stop. I have tried including a counter, sticking a safeword at the bottom of column A and stopping at the first empty cell in column A, but it keeps going off into infinity and crashing. I am not sure what i am missing as i have looked at lots of different forums about loops.

I don't mind which solution works provided one does, although my preference would be for the counter.

I have included the other macro that it calls in case that is the problem. Please bear with me i am very new at this, so don't be afraid of being condescendingly basic in your explanation.

Sub loop_removerows()


Dim counter As Integer
Dim endpoint As Long


counter = 0
endpoint = CLng(50000)


Do While counter < endpoint




ActiveCell.Offset(1, 0).Select

Do
If ActiveCell.Value = "Keep" Then Exit Do
If ActiveCell.Value <> "Keep" Then Selection.EntireRow.Delete
counter = counter + 1
Loop

Call skip_table


Loop


End Sub


Sub skip_table()


Do
If ActiveCell.Value = "End keep" Then Exit Do
If ActiveCell.Value <> "End keep" Then
ActiveCell.Offset(1, 0).Select
End If
Loop While ActiveCell.Value <> "End keep"


End Sub

 

[Michael M]

Hi and welcome to the Forum
When deleting rows, it's always best to start at the last row and work backwards, more efficient AND faster

Sub Delrow()
Dim lr As Long, r As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "A").End(xlUp).Row
    For r = lr To 1 Step -1
        If Range("A" & r).Value <> "Keep" Then
        Rows(r).Delete
        End If
    Next r
Application.ScreenUpdating = True
End Sub

 

[Scarteg]

Hi Michael

Thanks for your response that was super quick to my first post. I see your point about working up and i think i follow your macro. The reason i was working down (apart from ignorance) is because what i am working on is a set of excel tables. And essentially i want to delete some and keep others, so at the moment when i get to a row that says keep it then calls the other loop which moves down until it finds the word (not keep) to start deleting again. I am not sure that if it was working upwards it would be able to do this.

I have (hopefully) put an example snippet of what i mean below. The macro works down column A stopping and starting delete rows to keep only the "lifestage" tables. I need a macro to do this because there are 36,000 rows of tables.

Thank you


[TABLE="class: grid, width: 576"]
<tbody>[TR]
[TD]Keep[/TD]
[TD]Keep[/TD]
[TD="colspan: 2"]Q.A2 Gender[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="colspan: 2"]Base: All respondents[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD="colspan: 6, align: center"]Life-Stage Segment I[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD]Young Adults[/TD]
[TD]Singles & Couples[/TD]
[TD]Young Families[/TD]
[TD]Older Families[/TD]
[TD]Empty Nesters[/TD]
[TD]Retired[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Unweighted base[/TD]
[TD]221[/TD]
[TD]169[/TD]
[TD]378[/TD]
[TD]389[/TD]
[TD]397[/TD]
[TD]459[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Weighted base[/TD]
[TD]217[/TD]
[TD]188[/TD]
[TD]405[/TD]
[TD]385[/TD]
[TD]394[/TD]
[TD]424[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Male[/TD]
[TD]42%[/TD]
[TD]51%[/TD]
[TD]47%[/TD]
[TD]55%[/TD]
[TD]47%[/TD]
[TD]55%[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Female[/TD]
[TD]58%[/TD]
[TD]49%[/TD]
[TD]53%[/TD]
[TD]45%[/TD]
[TD]53%[/TD]
[TD]45%[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]End Keep[/TD]
[TD]End Keep[/TD]
[TD="colspan: 3"]Prepared by Bryter Ltd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="colspan: 2"]Return to Index[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Table 8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="colspan: 2"]Q.A2 Gender[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="colspan: 2"]Base: All respondents[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4, align: center"]Any Products[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD]First Direct[/TD]
[TD]Other[/TD]
[TD]Nationwide[/TD]
[TD]NotNationwide[/TD]
[TD]Barclays[/TD]
[TD]Co-Op[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Unweighted base[/TD]
[TD]73[/TD]
[TD]185[/TD]
[TD]459[/TD]
[TD]1554[/TD]
[TD]541[/TD]
[TD]168[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Weighted base[/TD]
[TD]65[/TD]
[TD]187[/TD]
[TD]420[/TD]
[TD]1593[/TD]
[TD]587[/TD]
[TD]155[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Male[/TD]
[TD]51%[/TD]
[TD]47%[/TD]
[TD]51%[/TD]
[TD]50%[/TD]
[TD]56%[/TD]
[TD]52%[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Female[/TD]
[TD]49%[/TD]
[TD]53%[/TD]
[TD]49%[/TD]
[TD]50%[/TD]
[TD]44%[/TD]
[TD]48%[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]End Keep[/TD]
[TD]End Keep[/TD]
[TD="colspan: 3"]Prepared by Bryter Ltd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="colspan: 2"]Return to Index[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD="colspan: 2"][Date] 30 Jan 2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Table 9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="colspan: 2"]Q.A2 Gender[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="colspan: 2"]Base: All respondents[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="colspan: 2"]Unweighted base[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Weighted base[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Male[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Female[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]End Keep[/TD]
[TD]End Keep[/TD]
[TD="colspan: 3"]Prepared by Bryter Ltd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="colspan: 2"]Return to Index[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Table 10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Keep[/TD]
[TD]Keep[/TD]
[TD="colspan: 5"]Q.A3a Which statement describes your working status?[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="colspan: 2"]Base: All respondents[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD="colspan: 6, align: center"]Life-Stage Segment I[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD]Young Adults[/TD]
[TD]Singles & Couples[/TD]
[TD]Young Families[/TD]
[TD]Older Families[/TD]
[TD]Empty Nesters[/TD]
[TD]Retired[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Unweighted base[/TD]
[TD]221[/TD]
[TD]169[/TD]
[TD]378[/TD]
[TD]389[/TD]
[TD]397[/TD]
[TD]459[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Weighted base[/TD]
[TD]217[/TD]
[TD]188[/TD]
[TD]405[/TD]
[TD]385[/TD]
[TD]394[/TD]
[TD]424[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]NET: Employed[/TD]
[TD]41%[/TD]
[TD]79%[/TD]
[TD]75%[/TD]
[TD]63%[/TD]
[TD]60%[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Full time employment (30+ hours per week)[/TD]
[TD]31%[/TD]
[TD]69%[/TD]
[TD]64%[/TD]
[TD]51%[/TD]
[TD]46%[/TD]
[TD]2%[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Part time employment (8-29 hours per week)[/TD]
[TD]10%[/TD]
[TD]9%[/TD]
[TD]11%[/TD]
[TD]12%[/TD]
[TD]15%[/TD]
[TD]8%[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]NET: Not working[/TD]
[TD]59%[/TD]
[TD]21%[/TD]
[TD]25%[/TD]
[TD]37%[/TD]
[TD]40%[/TD]
[TD]90%[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Not working, but seeking work/ temporarily unemployed[/TD]
[TD]8%[/TD]
[TD]7%[/TD]
[TD]5%[/TD]
[TD]1%[/TD]
[TD]5%[/TD]
[TD]1%[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Full time education/ student (35+ hour per week)[/TD]
[TD]45%[/TD]
[TD]-[/TD]
[TD]*[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Unemployed[/TD]
[TD]5%[/TD]
[TD]11%[/TD]
[TD]2%[/TD]
[TD]6%[/TD]
[TD]10%[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Housewife/ househusband/ house person[/TD]
[TD]1%[/TD]
[TD]3%[/TD]
[TD]17%[/TD]
[TD]17%[/TD]
[TD]12%[/TD]
[TD]3%[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]Retired[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]13%[/TD]
[TD]13%[/TD]
[TD]86%[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]End Keep[/TD]
[TD]End Keep[/TD]
[TD="colspan: 3"]Prepared by Bryter Ltd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

With Merged cells AND the use of "FALSE" in the columns it made it a little difficult.
But I came up with a double For / Next.....I'm sure there will be a better way from others, but try...

Sub Delrow()
Dim lr As Long, r As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "A").End(xlUp).Row
    For r = 1 To lr
        If Range("A" & r).Value = "Keep" And Range("A" & r + 1).Value <> "End Keep" Then
            Range("A" & r + 1).Value = "Keep"
        ElseIf Range("A" & r).Value = "End Keep" And Range("A" & r + 1).Value <> "Keep" Then
            Range("A" & r + 1).Value = "End Keep"
        End If
    Next r
lr = Cells(Rows.Count, "A").End(xlUp).Row
    For r = lr To 1 Step -1
    If Range("A" & r).Value = "End Keep" Then
            Rows(r).Delete
        End If
    Next r
Application.ScreenUpdating = True
End Sub

 

[Scarteg]

Thank you i will give it a try.