Excel VBA Delete all blank rows

This VBA snippet is very useful to have, and in most projects, it is common for us to have this code to hand, and adapt as required to carry out a specific task on a dataset.

There may be situations where your large dataset has been cleaned as far as possible, but there are still elements in the data that have no value, or relatively little value and it would be beneficial to remove the data completely but still keep a clean and tidy dataset.

This code can also be adapted to remove rows of data where a specific condition is met, such as a cell has the word – duplicate for instance. It is easy enough to adapt the code, and the example below is the most basic we feel that covers the principal of what it needs to do – delete rows where col A is blank.

We would recommend using the lastrow function that is covered in another blog post to make it more efficient but if you know how many rows of data you have, no reason why this code won’t do just as good a job.

This particular code looks at the value in each cell from 100 up to 1 in Column A on the tab “Data”. If it detects that the particular cell is blank (or “”) it takes the entire row, deletes it and shifts the data below up to keep it all nice and tidy. You’ll notice that the loop we use starts at row 100 or at the bottom and makes it’s way up to the top in steps of -1. This is because the overriding feature of this process is to detect a row and remove it, bringing the data below it up 1 row each time. If we were to start at the top and go through each row down to the bottom we would inevitably miss rows where we had deleted and the data below had jumped up 1 row. Because we have already decided a row is to be deleted, Excel will delete it and agree with itself that the job on that row is done, move on to the next one. However, if the row that had just been moved up also fit the criteria to be deleted, Excel would just move on, content it had already done it’s job on that row. By going from the bottom to the top, this problem won’t exist.

Feel free to ask us a question, or leave a comment below and we’ll answer the question for everyone to see and learn from, maybe even us !

We have included the 2nd set of code to do exactly the same but use the lastrow function to make it more efficient.


Public Sub Delete_Blank_Rows_if_ColA_Blank()
For n = 100 To 1 Step -1
If Worksheets(“Data”).Range(“A” & n) = “” Then
Worksheets(“Data”).Range(“A” & n).EntireRow.Delete shift:=xlUp
End If
Next n
End Sub


Public Sub Delete_Blank_Rows_to_lastrow_if_ColA_Blank()
With Sheets(“Data”)
lastrow = .Cells(.Rows.Count, “A”).End(xlUp).Row
End With

For n = lastrow To 1 Step -1
If Worksheets(“Data”).Range(“A” & n) = “” Then
Worksheets(“Data”).Range(“A” & n).EntireRow.Delete shift:=xlUp
End If
Next n
End Sub


Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us

Datanology

Leave a Reply

Your email address will not be published. Required fields are marked *