M-TechRidge Blog

Delete Blank Rows Using Excel VBA (Fast and Easy)

Change Language:

Sometimes we export excel file that contain blank rows. If you try to delete these rows using filter and sort option they does not appears there. as you see in Image below:

blank row option in sort and filter excel

There are two ways to delete blank rows :

  1. Using VBA
  2. Without VBA

Using VBA:

Step 1:  Open Excel File that Contain Blank Rows.

Step 2: Press ALT + F11 to Open VBA Window.

Step 3: Go to Insert Menu and Click on Module.

vba insert menu module

Step 4: Now Copy and Paste This Code in Module

Sub Removerows()
  i = 0
  For i = 10 To 56
 If Worksheets(1).Cells(i, 1) = "" Then
  Worksheets(1).Cells(i, 1).Activate
    End If
    Next i
End Sub

vba insert menu module

Note: For i = 10 To 56 Means Table Data Starts From 10th Row and Ends on 56th Row. You can change this value as per your requirements. 

Step 5: Click on Run Button to run module and you see all blank rows has been deleted from active Sheet.

vba insert menu module

Video Tutorial (English):

Video Tutorial (Hindi):

Leave a Comment:

Validation code:

Can't read the image? click here to refresh.