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

This should do it if the sheet is already the active sheet:

Sub DeleteAllBlankCells()

    ActiveSheet.UsedRange.SpecialCells(xlBlanks).EntireRow.Delete

End Sub

Otherwise:

Sub DeleteAllBlankCells()

    Workbooks("Forms.xlsm").Worksheets("Sheet1").UsedRange.SpecialCells(xlBlanks).EntireRow.Delete

End Sub

Answer 4

Hi MVP,

So sorry for replying you so late  !

I tried both of the codes but i am getting this run time error '1004:  Cannot use that command on overlapping selections.

So i tried by searching in google why i am getting that error  "

 The code won't work if you have blank cells in more than one column on the same row. You'll have to loop through the columns."

With the below code i am able to delete blank cells which are present in the column.

Sub DeleteAllBlankCells()

Dim rgCol As Range

On Error Resume Next

For Each rgCol In Range("A1:V1").Columns

    rgCol.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Next rgCol

End Sub

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

Can u pls help me with this.

Thank you!

Regards

Wither

Answer 5

Thank you MVP it worked !