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.