Written by:

Written by:

Thomas Szigeti

Hey all!

Today we will discuss a VBA technique which we have to use every now and again.

This is when we have to delete row or rows in a workbook with VBA code.

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.

Well…

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?

YES!

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…

[et_pb_ccfcm_facebook_comments_module ccfcm_app_id=”339898187197719″ _builder_version=”4.6.1″ _module_preset=”default”][/et_pb_ccfcm_facebook_comments_module]