How to Delete Blank Rows Using Excel VBA?

Here is a simple problem, you have a set of data in an excel sheet, but there are many empty rows. What you want to do is delete these empty rows without changing the order of data or disturbing anything else.

If the order of the rows/data did not matter, removing empty rows was simply a matter of sorting the data. By sorting the data, all the empty rows will get together, which you can select and delete.

However, we are not interested in that. We don't want the data to be reordered. What we want excel to do is that when it sees an empty row, it deletes that data and shift the remaining data up.

This task can be done by writing a VBA macro.

Sub DeleteBlankRows1() Dim i As Long With Application .Calculation = xlCalculationManual .ScreenUpdating = False For i = Selection.Rows.Count To 1 Step -1 If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then Selection.Rows(i).EntireRow.Delete End If Next i .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub

You need to write this macro, then select the rows from which you want to delete the empty rows, and run this macros. Voila! The empty rows are gone.

Instead of making a selection, you can also specify the range of rows using the Range() function.

Learn the skills required to excel in data science and data analytics covering R, Python, machine learning, and AI.

Free Guides - Getting Started with R and Python

Enter your name and email address below and we will email you the guides for R programming and Python.

Saylient AI Logo

Take the Next Step in Your Data Career

Join our membership for lifetime unlimited access to all our data analytics and data science learning content and resources.