Finding yourself in a position to clean up a worksheet is quite common, and Excel users will sometimes want to remove rows of data that fit a certain criteria rather than just use the filters. It’s a lot cleaner to remove rogue or non-required rows from your data and good practice to do it with VBA.
The code below can be adapted and added to to reach your objective, but there are some ‘Golden rules’ when it comes to removing rows of data.
The first rule is to work from the bottom upwards. As Excel increments between the rows, if it gets to row 5 for example and deletes it due to triggering a rule, it brings what was row 6 up to now become row 5. Excel then looks for the next row which is 6 and completely ignores the new row 5 as it thinks it’s already dealt with it. You could add some code to reverse 1 row when it has triggered it’s rule but it is far more efficient to work upwards.
This code example deals with a fixed range rows 1 to 1000 but we would advise you use the ‘Last used row‘ code as well – click the text to go there.
Public Sub Delete_Blank_row
for n = 1000 to 1 step – 1
if Activesheet.Range(“A” & n) = “” then
Activesheet.Range(“A” & n).EntireRow.Delete Shift:=xlUp
Adapt the 2nd row of the code for last used row and/or the 3rd row to set another rule. You can use further functions to identify the first few letters, or if certain characters are in the cell.
Feel free to leave a message below or use the contact form to get in contact. We’d love to help you achieve your projects goal.