How To Delete Empty Rows In Excel Vba

In Excel VBA, you can delete empty rows using the following code:

Sub DeleteEmptyRows()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' Set the worksheet object
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your sheet name
    
    ' Get the last row in the sheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row in reverse order
    For i = lastRow To 1 Step -1
        ' Check if the entire row is empty
        If WorksheetFunction.CountA(ws.Rows(i)) = 0 Then
            ' Delete the row
            ws.Rows(i).Delete
        End If
    Next i
End Sub

Here’s a detailed explanation of the code:

1. First, we declare variables for the worksheet, the last row, and a counter.
2. We set the worksheet object by referencing the specific sheet name. Replace “Sheet1” with the name of your sheet.
3. The lastRow variable is set to the last non-empty row in column A using the End(xlUp) method.
4. Next, we loop through each row in reverse order, starting from the last row and going up to the first row.
5. Inside the loop, we use the CountA function to check if the entire row is empty. If the count is 0, it means the row is empty.
6. If the row is empty, we delete it using the Delete method on the Rows object.
7. Finally, we move on to the next row by decrementing the counter i by 1 and repeating the process.

Make sure to adjust the sheet name and any other relevant parameters in the code to match your specific workbook setup.

Here’s an example:

After running the code, the empty rows will be deleted: