Removing Row Based on Blank Cells

Question

Is there an easy way to tell Excel to delete a row if a certain cell in an intersecting column is blank? Would this require VBA?

As a reference point, I found the following VBA code:

Dim FoundCell As Range
    Application.ScreenUpdating = False
    Set FoundCell = Range("B:B").Find(what:="")
    Do Until FoundCell Is Nothing
        FoundCell.EntireRow.Delete
        Set FoundCell = Range("B:B").FindNext
    Loop

but for some reason it crashes when I try to run it.

SuperUser.com might also be a good place to ask. http://meta.stackexchange.com/questions/90919/which-stack-exchange-site-is-best-for-microsoft-excel-questions — Kirby, Mar 24 '14 at 20:05
so, you need to delete row if value in column B is blank or "X"? — Dmitry Pavliv, Mar 24 '14 at 20:07
look into this answer: http://stackoverflow.com/questions/22541846/delete-blank-rows-after-autofilter/22541933#22541933 — Dmitry Pavliv, Mar 24 '14 at 20:08
@simoco That works perfectly, I should have checked more carefully for similar problems. — 114, Mar 24 '14 at 20:25

score 0 · Accepted Answer · answered Mar 24 '14 at 20:34

If you wish to delete all rows in which the value in column B is blank, then try:

Sub KleanB()
    Dim B As Range, rDel As Range, r As Range, N As Long
    N = Cells(Rows.Count, "B").End(xlUp).Row
    Set B = Range("B1:B" & N)
    B.Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

score 0 · Answer 2 · answered Mar 25 '14 at 10:55

0

For a non-VBA solution:

Select the column where the blanks may appear. Press F5 (GoTo), click Special and select Blanks. Click OK. Only blank cells will be select. Right click on one of the blank cells and click Delete Row.

answered Mar 25 '14 at 10:55

Excel Developers

2,785
2
21
35

Removing Row Based on Blank Cells

2 Answers2