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>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube