Excel VBA Delete Blank Row

Sometimes you have data in Excel with blank cells. For example, let’s say you have a simple database in excel worksheet with data from column A to column D. The first row of your database has titles for each column and your data starts from cell A2 and goes all the way to cell D100.

However you need to prepare report showing only the information with all the required data from column A which means that your database for this report is not useful if there is no data in column A.

You can create a copy of your worksheet for the report and use the following macro to delete all rows without data in Column A:

Sub Delete_Rows_with_Blank_Cells()

Related: Excel

       Range(Range(“a65536”).End(xlUp), Range(“A2”)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

How this excel vba macro works?

This excel macro is looking for every blank cell in Column A and deletes the entire row where the respective cell in column A is blank. The macro uses the End(xlUp) to find the last nonblank cell in Column A but you can replace this with another fixed range value based on your needs.

Another simple approach to deleting rows with blank cells in column A is without using excel VBA. Simply use the filter tool and sort the data from cell A2 and you can easily select and delete all rows with blank cells in column A.