Deleting entire row if the cell in column N is blank

jarnold231 · #1 (**permalink**) 08-22-11, 11:21

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:

Delete the entire row is the cell in column A is blank

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.

scrtchmstj · #2 (**permalink**) 08-22-11, 11:31

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.

jarnold231 · #3 (**permalink**) 08-22-11, 11:48

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.

scrtchmstj · #4 (**permalink**) 08-22-11, 12:22

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech