How To Delete Blank Rows In Excel Vba

How to Delete Blank Rows in Excel VBA

Deleting blank rows in Microsoft Excel using VBA (Visual Basic for Applications) can be done through a few simple steps. Here’s an example:

Sub DeleteBlankRows()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' Set the worksheet to work with
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with your sheet name
    
    ' Find the last used row in the worksheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row from the bottom to the top
    For i = lastRow To 1 Step -1
        ' Check if the entire row is blank
        If Application.WorksheetFunction.CountA(ws.Rows(i)) = 0 Then
            ' Delete the entire row if it is blank
            ws.Rows(i).Delete
        End If
    Next i
End Sub
    

Explanation:

• Sub DeleteBlankRows() starts the VBA subroutine.
• Dim ws As Worksheet defines a variable to hold the worksheet object.
• Dim lastRow As Long and Dim i As Long define variables to hold the last used row and the loop counter, respectively.
• Set ws = ThisWorkbook.Worksheets("Sheet1") sets the worksheet to work with. Replace “Sheet1” with the name of your sheet.
• lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row finds the last used row in the worksheet by starting from the bottom of column A and moving up until a non-blank cell is encountered.
• The For i = lastRow To 1 Step -1 loop iterates through each row from the bottom to the top.
• If... checks if the entire row is blank by using the CountA() function, which counts the number of non-blank cells in a range. If the count is 0, it means the entire row is blank.
• ws.Rows(i).Delete deletes the entire row using the Delete method of the Rows object.

To use this code, follow these steps:

1. Press ALT + F11 to open the Visual Basic Editor in Excel.
2. Insert a new module by clicking Insert > Module.
3. Paste the code into the module.
4. Press F5 or click Run > Run Sub/UserForm to execute the code.