Delete Rows in VBA

In this article we will learn how to delete the rows when there is no data in whole row.

Following is the snapshot of data we have:

The concept behind this tutorial is if there is any complete blank row found in the selected range A1:E10 then the vba code should delete that row.

We need follow the below steps:

• Click on Developer tab
• From Code group, select Visual Basic

• Enter the following code in the current worksheet module

Sub DeleteEntireRow()

Dim i As Long

    'Calculation and Screenupdating is turn off to speed up the macro

    With Application

        .Calculation = xlCalculationManual

        .ScreenUpdating = False

    For i = Selection.Rows.Count To 1 Step -1

        If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then

            Selection.Rows(i).EntireRow.Delete

        End If

    Next i

        .Calculation = xlCalculationAutomatic

        .ScreenUpdating = True

    End With

End Sub

• Let us first manually delete the data in a row to test our code.
• After copying the code; select the range A1:E10 & manually delete any row

• Now run the macro using ALT + F8 key & select DeleteEntireRow

• The empty row has been deleted & all the below rows are shifted upwards.

In this way we can delete rows using vba in Microsoft excel.

Download-Delete Rows in VBA