Deleting rows in VBA is a bit tricky because if you start looping through the rows and delete one, then the one below will move up, and it will carry the row number of that you just deleted.
What we have to do is to start looping upwards. Here it is how we going to achieve this.
We need to find the last row where we can work from
We also need some kind of a flag that we can evaluate. If it is true we will delete the row.
A bit of explanation to the code on the right.
We find the lastrow.
Note: this is happening in column A. If your dataset needs it differend replace the “A” to another column.
Then we start looping from lastrow to 1
Note: Again change row number 1 to suit your dataset.
Also note: We added Step -1 to loop from backwards.
Then we evaluate if we need to delete the row
If the column you looping is marked with the word “del” in the cells then it gets deleted.
Note: In the row that does the delete, the Column “A” is indifferent as the row number is carried with i variable
Delete rows with Excel VBA
Sub DeleteMe Dim lastrow As Long Dim i As Long lastrow = Cells(Rows.Count, "A").End(xlUp).Row For i = lastrow To 1 Step -1 If (Cells(i, "D").Value) = "del" Then Cells(i, "A").EntireRow.Delete End If Next i End Sub
Chek out the code in action!
Can I modify on how I mark the rows for deletion?
You just need to replace the word del in the code you will copy.
Come and visit us.
Come and visit excelangel on Facebook to find and join the growing community of true office workers.
Oh, I almost forgot.
Drop us a like while you there…