Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: DELETE ROWS BASED ON BLANK CELLS IN COL "A"

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    OKC
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am trying to use some existing code to do this but I am having no luck. I need a macro that will search all of column A and delete rows if the cells in COL A are blank. thanks....

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here's a non VBA solution:

    1. Add another column with sequential numbers (values) to your worksheet.
    2. Sort the worksheet by column A. This will place all the rows with blanks in column A together.
    3. Highlight and delete the rows with blanks in column A.
    4. Re-sort the worksheet based on the column you added in step 1.
    5. Delete the column you added in step 1.

    _________________
    It's never too late to learn something new.

    Ricky

    [ This Message was edited by: Ricky Morris on 2002-04-25 09:06 ]

  3. #3
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this. JSW

    Sub DRow()
    'Find all the rows for which column "A" = "" and delete it.

    Application.ScreenUpdating = False
    Worksheets("Sheet1").Select
    Range("A1").Select
    For Each r In Worksheets("Sheet1").UsedRange.Rows
    n = r.Row
    If Worksheets("Sheet1").Cells(n, 1) = "" Then
    Worksheets("Sheet1").Cells(n, 1).Select
    Selection.EntireRow.Delete
    End If
    Next r
    Application.ScreenUpdating = True
    'Range("A1").Select
    End Sub


    [ This Message was edited by: Joe Was on 2002-04-25 10:10 ]

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    OKC
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Joe,

    Your code stops after the first few rows are deleted. any suggestions?

  5. #5
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello Eddie

    download the very useful addin asap utilities
    from

    http://www.asap-utilities.com

    it is free and works great for excel stuff like that



    Andreas

  6. #6
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    The code looks for an empty cell in column A. If you have a blank in a cell in column A the code will skip that row.

    Move your cursor to one of the skiped rows and delete the contents of a missed cell in column A, then re-run the the code. I would mark the cell to the right to test. If the code removes your cleaned up cell row then thats the problem. The test cells are not truly empty?

    Modify the code with a " " inplace of the "". Then add a copy of the code below the first macro but inside the Sub. That should fix it? JSW

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Oregon
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Why not just autofilter column A, display the blanks & then delete those rows? I know that's pretty simplistic, but it works for me.

    And the ASAP utilities are well worth downloading. A lot of time-saving features in there!

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is fairly quick:

    Code:
    Sub deltime2()
    [a:a].SpecialCells(xlCellTypeBlanks).EntireRow.delete
    End Sub
    Hope this helps.

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    OKC
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nate, thanks, that is exactly what i am looking for.........thanks to all else who responded.......is this a great board or what???

  10. #10
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You're welcome Eddie, have a good one. I concur, this is good stuff!

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-04-25 18:48 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •