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
Dim i As Long
‘Calculation and Screenupdating is turn off to speed up the macro
.Calculation = xlCalculationManual
.ScreenUpdating = False
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
- 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.