• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

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
0
AndreasHermle
Asked:
AndreasHermle
1 Solution
 
SteveCommented:
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

0
 
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

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now