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

Posted on 2012-08-14
Last Modified: 2012-08-14
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
Question by:AndreasHermle
    LVL 24

    Accepted Solution

    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

    LVL 8

    Expert Comment

    by:Elton Pascua
    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


    Author Closing Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now