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:

image 1

 

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

 

image 2

 

  • 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

 

image 3

  • 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

image 4

 

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

image 5

  • 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.

ExcelDownload-Delete Rows in VBA



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>