Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011
    Posts
    3

    Unanswered: Deleting entire row if the cell in column N is blank

    Hi there this is my first posting to this forum but I'm hoping to get some help with a piece of code I found on an old posting on this forum:

    http://www.dbforums.com/microsoft-ex...umn-blank.html

    What I am attempting to do is very similar - use VBA to check a large sheet of varying size and remove any rows that do not have information in the cell in column N.

    The code I have got is:

    Range ("n1:n5500").Select
    Do
    If ActiveCell = "" Then
    ActiveCell.EntireRow.Delete shift:=xlUp
    ActiveCell.Offset(-1, 0).Select
    End If
    ActiveCell.Offset(1, 0).Select
    Loop Until n = n5500

    The problem that I'm having is that either the loop doesn't end or for some reason if doesn't delete the rows at all and stops immediately.

    Any help greatly appreciated.

  2. #2
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    There is probably a better way to do this but I use the following:

    Code:
        'THIS DELETES ALL ROWS WHERE THE VALUE IN COLUMN B IS "X"
        Dim FoundCell As Range
        Application.ScreenUpdating = False
        Set FoundCell = Range("B:B").Find(what:="X")
        Do Until FoundCell Is Nothing
            FoundCell.EntireRow.Delete
            Set FoundCell = Range("B:B").FindNext
        Loop
    I'm guessing the reason your code is not ending is because as you delete rows where the column N value is null, Excel is just adding additional rows at the end which will also have a null value for column N. I'm actually not sure how my code above will work with a null value, it may behave similarly.
    Last edited by scrtchmstj; 08-22-11 at 12:53.

  3. #3
    Join Date
    Aug 2011
    Posts
    3
    Thanks for the response - that didn't work for me - just seemed to crash the sheet. Not sure if its the macro or something else as the sheet is enormous at the moment.

    I found an alternative fix which was to add

    Loop Until IsEmpty(ActiveCell.Offset(1, 0))Loop Until IsEmpty(ActiveCell.Offset(2, 0))Loop Until IsEmpty(ActiveCell.Offset(3, 0))

    and repeat that until #10.

    Its a bit slow and laborious but seems to work.

    Thanks for your help.

  4. #4
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Xtreme Visual Basic Talk - View Single Post - Excel VBA: How To Delete Rows

    Also found this. Is much faster than what I had above if you have many results.
    Last edited by scrtchmstj; 08-22-11 at 13:30.

Tags for this Thread

Posting Permissions

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