We help IT Professionals succeed at work.

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

Andreas Hermle
on
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
Comment
Watch Question

Cost Accountant
CERTIFIED EXPERT
Top Expert 2012
Commented:
Something like this should do it in vba:

Sub test()

Dim ws As Worksheet
Set ws = ActiveSheet

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

End Sub

Open in new window

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 leader

Author

Commented:
Great job. Works like a charm. Thank you very much for your professional help. Regards, Andreas

Explore More ContentExplore courses, solutions, and other research materials related to this topic.