Deleting Blank Rows Using VBA

One of the things that Really takes time at work is data cleanup

from a Database Export. Many Excel Users spend perhaps even hours deleting

Unwanted Rows in an effort to make the data useful. The following code is handy

for those seeking a VBA solution to their data problems.



Sub DeleteBlankRows()

Dim LastRow As Long, MyRow As Long

LastRow = ActiveSheet.UsedRange.Rows.Count
LastRow = LastRow + ActiveSheet.UsedRange.Row - 1

For MyRow = LastRow To 1 Step -1
If WorksheetFunction.CountA(Rows(MyRow)) = 0 Then Rows(MyRow).Delete
Next MyRow

End Sub

The Procedure uses the LastRow Variable and the MyRow Variable in a Next Loop to test each row based off the CountA Function. This Function runs a test and if the answer is 0 for each of the cells in the row being blank then it deletes the row.

There is a fair bit of learning in this example. e.g Variables, Variable Types, Loops and If Statements to name some of them. The VBA Course covers all of these learning outcomes.

0 Comment

Leave a Reply

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