Delete rows on condition vba

Sometimes, when you import information to an worksheet from another workbook, empty rows (because of a formula which returns a ‘0’) or perhaps unwanted information could be imported as well. I make use of the following code to ‘clean’ the imported data. It is easy to extend and adapt the code to your needs. I will show you how:

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

LR = Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
If Range("A" & i).Value = 0 And Range("C" & i).Value = 1900 Then Rows(i).Delete

Next i

End Sub

Breaking down the code: 

We use “Dim” to declare a variable. Long is used to store greater numbers than integers. Dim LR as long combined with: “& Rows.Count).End(xlUp).”  is used to set to the last row of a dataset, counting in Column A, of the worksheet “Data”. You should change “Data” to the name of your worksheet.

“For i = LR to 1 Step -1” is best explained in the following way. When we do not use this line, the code will work itself through the sheet from top to bottom. Deleting a row could disrupt the loop. By using Step -1 the code loops itself in reverse through the code, thereby not disrupting the code when a row is deleted.

“If Range(“A” & i).Value = 0 And Range(“C” & i).Value = 1900 Then Rows(i).Delete” This line specified the condition(s). When in column A “0”  and in column C “1900” is found then delete that (“that” row is stored in the variable “i”) row. The dataset I used contains sometimes dates like 1-1-1900 (That is a 0 transformed to a date in Excel). So in order to delete it I used “1900” as a condition. You could easily extend the code by adding more “And” variables. You could make us of the “Or” variable.




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s