Here’s a step-by-step guide to delete blank rows using VBA:

Step 1: Open Excel and Enable the Developer Tab

1. Open Excel and load your workbook.
2. Enable the Developer tab if it’s not visible:
   • Go to File > Options.
   • In the Excel Options dialog box, click Customize Ribbon.
   • On the right side, check Developer and press OK.

Step 2: Open the VBA Editor

1. Click on the Developer tab.
2. Click Visual Basic or press Alt + F11 to open the VBA editor.

Step 3: Insert a New Module

1. In the VBA editor, click on Insert in the top menu.
2. Select Module to add a new module.

Step 4: Write the VBA Code to Delete Blank Rows

Copy and paste the following VBA code into the module:

Sub DeleteBlankRows()
    Dim i As Long
    Dim lastRow As Long

    ' Find the last row with data in column A
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    ' Loop from the last row to the first row
    For i = lastRow To 1 Step -1
        ' Check if the entire row is blank
        If WorksheetFunction.CountA(Rows(i)) = 0 Then
            Rows(i).Delete
        End If
    Next i
End Sub

Code Explanation:

• lastRow = Cells(Rows.Count, 1).End(xlUp).Row: This line finds the last row with data in column A.
• For i = lastRow To 1 Step -1: Loops through each row from the last row to the first row.
• If WorksheetFunction.CountA(Rows(i)) = 0: Checks if the entire row is empty (no values).
• Rows(i).Delete: Deletes the row if it is blank.

Step 5: Run the Macro

1. After pasting the code, close the VBA editor or minimize it.
2. Go back to the Excel workbook.
3. Press Alt + F8 to open the Macro dialog.
4. Select DeleteBlankRows from the list and click Run.

Step 6: Verify the Results

• The macro will go through each row in your sheet, starting from the last row, and delete any rows that are completely blank.

Notes:

• The code deletes rows that are entirely blank. If a row has data in even one cell, it won’t be deleted.
• If you want to target a specific column for blank rows instead of the entire row, you can modify the If condition to check only that column, like this

If Cells(i, 1).Value = "" Then
    Rows(i).Delete
End If

• This will delete rows where column A is empty.