Conditionally delete rows (where there is no entry in Column A)

Dear Experts:

I got a table with thousands of data records with the following make up

Art-No           Item           Quantity
145            Product A         3  
149            Product C         5  
124            Product B       17
                  Product X        12
147            Product F        14
                  Product Y        17
214            Product D       13            

I would like to delete all the rows where there is no entry in Column A using a VBA macro.

Help is much appreciated.

Thank you very much in advance.

Regards, Andreas
Andreas HermleTeam leaderAsked:
Who is Participating?
SteveConnect With a Mentor Commented:
Something like this should do it in vba:

Sub test()

Dim ws As Worksheet
Set ws = ActiveSheet

ws.UsedRange.AutoFilter Field:=1, Criteria1:=""
Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp

End Sub

Open in new window

Elton PascuaCommented:
Here's another approach.

Sub DeleteBlankRows()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.Range("A1:A" & ws.Range("A1000000").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

Open in new window

Andreas HermleTeam leaderAuthor Commented:
Great job. Works like a charm. Thank you very much for your professional help. Regards, Andreas
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.