Delete specified consecutive rows after a found value

Question

I want to delete specified number of rows after a found value in a column. I find a search value in A3 then I want to delete rows from 4th row to specified number of rows say from 4 th row to 11th row. My code deletes alternate rows. If I specify ~Rows("cnt1:cnt2") it gives error. Need guidance to correct the error in my code. Images before and after code run are given below. It should delete consecutive rows containing aa,bb,cc,dd.....

Code followed by me is given below.

 Sub Asearch()
Dim found As Range
Dim FR As Long
Dim cnt1 As Long
Dim cnt2 As Long
   Set found = Sheets("Sheet1").Columns("A").Find(what:="Swati", LookIn:=xlValues, lookat:=xlWhole)
    If found Is Nothing Then
       MsgBox "Not found"
       Else
       MsgBox "Found on row " & found.Row
       FR = found.Row
       cnt1 = FR + 1
       Debug.Print cnt1
       cnt2 = 11
       Do While cnt1 <= cnt2
          Rows(cnt1).EntireRow.Delete
          cnt1 = cnt1 + 1
          Debug.Print cnt1
       Loop
    End If
End Sub

EDIT of the post based on @micstr guidance. Problem is solved. Updated code is given below.

Sub Asearch2()
Dim found As Range
Dim FR As Long
Dim cnt1 As Long
Dim cnt2 As Long
   Set found = Sheets("Sheet1").Columns("A").Find(what:="Swati", LookIn:=xlValues, lookat:=xlWhole)
    If found Is Nothing Then
       MsgBox "Not found"
       Else
       MsgBox "Found on row " & found.Row
       FR = found.Row
       cnt1 = FR + 1
       Debug.Print cnt1
       cnt2 = 11
       Do While cnt2 >= cnt1
          Rows(cnt2).EntireRow.Delete
          cnt2 = cnt2 - 1
          Debug.Print cnt2
       Loop
    End If
End Sub

micstr · Accepted Answer · 2016-02-20 08:22:02Z

1

You have an error in your loop as you keep pushing the counter forward. As you delete entire row the index moves up. So

1 aa
2 bb
3 cc

As you delete aa, bb moves up to place 1 so now adding one to counter cntl is looking now at position 2. And you delete cc leaving bb. Which has moved up to one place.

1 bb
2 cc

To fix this perhaps you want to rather mark All Rows at once and delete them as one block. Or stay with Delete Entire Row and fix how you are moving your counters. (Thinking about it you will see cnt2 is changing i.e. moving up cnt2 = cnt2 - 1 as you delete rows).

Or another approach which may be easier to debug is to start from the bottom and work upwards not downwards.

Let me know if you need further help.

edited Feb 20, 2016 at 8:22

answered Feb 20, 2016 at 8:16

micstr

5,2568 gold badges52 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

skkakkar Over a year ago

I tried number of rows delete by trying code ~Rows("cnt1:cnt2").EntireRow.Delete~ but it gives error , seems not to be correct syntax. Please suggest the code modification for this purpose . My level in VBA is basic only. please help.

skkakkar Over a year ago

Thanks for your valuable guidance. Problem is solved and I have uploaded the edited code.

micstr Over a year ago

Great - glad you cracked it. Debugging can be hell sometimes!

Collectives™ on Stack Overflow

Delete specified consecutive rows after a found value

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related