VBA delete entire row if a cell is blank

Question

Hi,

i want to delete the entire row when a cell within the row is blank.

So ,I tried it but the below code is not working.

Sub DeleteAllBlankCells()

    Windows("Form.xlsm").Activate

    Sheets("Sheet1").Select

    Cells.Select

    Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete

End Sub

You can download the workbook by Clicking here.

Thank You!

Regards

Wither

Answer 1

> ... You'll have to loop through the columns."

Sub DeleteRowsWithBlanks()

    Dim Rng As Range

    On Error Resume Next ' In case there are no blanks

    Set Rng = [A:V].SpecialCells(xlCellTypeBlanks).EntireRow

    Intersect(Rng, Rng).Delete

    ActiveSheet.UsedRange 'Reset

End Sub

Answer 2

Try this version:

Sub DeleteAllBlankCells()

    Dim r As Long

    Dim m As Long

    Application.ScreenUpdating = False

    m = Range("A:V").Find(What:="*", SearchOrder:=xlByRows, _

        SearchDirection:=xlPrevious).Row

    For r = m To 2 Step -1

        If Application.CountIf(Range("A" & r & ":V" & r), "") > 0 Then

            Range("A" & r).EntireRow.Delete

        End If

    Next r

    Application.ScreenUpdating = True

End Sub

Answer 3

Thank you  Dana ! (;-)

Answer 4

Newbie here:  can you provide the step-by-step to set this up from start to finish?  I have been experimenting but I keep getting syntax errors.  In my worksheet, if the value of the cell in column E is blank or zero, I want to delete the entire row.  I would appreciate any help!

Answer 5

Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module or press Alt+I, M to insert an empty new code module.

Copy the following code into the module:

Sub DeleteBlanks()

    Dim r As Long

    Dim m As Long

    Application.ScreenUpdating = False

    m = Range("E" & Rows.Count).End(xlUp).Row

    For r = m To 1 Step -1

        If Range("E" & r).Value = "" Or Range("E" & r).Value = 0 Then

            Range("E" & r).EntireRow.Delete

        End If

    Next r

    Application.ScreenUpdating = True

End Sub

One way to run this macro is to click anywhere in the code and to press F5.

Another way:

Switch back to Excel (Alt+F11).

Press Alt+F8 to activate the Macros dialog.

Select DeleteBlanks, then click Run.

Remark: if you want to keep the macro for later (re)use, make sure that you save the workbook as a macro-enabled workbook (.xlsm).