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

Did as instructed, but when I press F5 I get "Syntax Error" and it highlights the first line "Dim r As Long".

Answer 4

That is weird - I have tested the code. Try copying and pasting the code into Notepad first, then copy it from there and paste it into the code module.

Answer 5

Thank you!  That did the trick!