Delete Blank Rows Using VBA

While working with Excel, you often encounter blank rows in a dataset that can cause adverse effects such as inaccurate calculations and analysis.

Although you can clean the dataset by removing the blank rows using formulas and the user interface options, using the ‘Rows.Delete‘ method in VBA is often faster and more efficient.

In this tutorial, I will show you eleven examples of deleting blank rows in Excel using VBA.

Note: I highly recommend creating a data backup before deleting blank rows using VBA, as VBA changes are irreversible. The backup will ensure the availability of your original data should you need it in the future.

Delete Blank Rows From a Specific Range

Assuming you have blank rows in the cell range A1:K100 on ‘Sheet2’ of the current workbook. You can use the following code to delete the blank rows:

Sub DeleteBlankRowsInRange()

    ' Declares worksheet and range variables
    Dim ws As Worksheet
    Dim rng As Range
    Dim row As Long

    ' Sets the worksheet and range to work with
    Set ws = ThisWorkbook.Sheets("Sheet2")
    Set rng = ws.Range("A1:K100")

    ' Loops through the range from the bottom row to the top
    For row = rng.Rows.Count To 1 Step -1
        ' Checks if the entire row within the range is blank
        If Application.WorksheetFunction.CountA(rng.Rows(row)) = 0 Then
            ' Deletes the row if it is blank
            rng.Rows(row).Delete
        End If
    Next row

End Sub

The above VBA subroutine is designed to delete all blank rows within a specific range in an Excel worksheet.

It targets the worksheet “Sheet2” and focuses on the range “A1:K100”. The script loops through this range from the last row upwards, checking each row for any content.

It uses the CountA function to determine if a row is entirely blank (i.e., contains no data). If a row is found to be blank, it is deleted.

Note: When you delete a row in Excel, the rows below it shift up to fill the gap left by the deleted row. Deleting rows from the top down can cause problems because the row numbers dynamically change as you delete them. As a result, you might skip or delete rows you didn’t intend to.

Delete Blank Rows in a Specific Worksheet of the Current Workbook

Suppose you have data with blank rows on ‘Sheet1’ of the current workbook and want to delete the blank rows. Below is a VBA code you can use to do this:

Sub DeleteBlankRowsInWorksheet()

    ' Declares variables
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    ' Sets the target worksheet to "Sheet1"
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' Determines the last used row in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Loops backwards from the last row to the first
    For i = lastRow To 1 Step -1
        ' Checks if the entire row is blank
        If WorksheetFunction.CountA(ws.Rows(i)) = 0 Then
            ' Deletes the row if it is blank
            ws.Rows(i).Delete
        End If
    Next i

End Sub

The above VBA macro code removes all blank rows from a specific worksheet (“Sheet1”) in the current workbook.

It first identifies the last used row in column “A” to determine the range for checking blank rows. It then iterates from this last row up to the first row, examining each row for any content.

It uses the CountA function to check if a row is completely blank. If a row contains no data, it is deleted.

The approach of starting from the last row and moving upwards helps to prevent any potential issues with changing row indices after deletions.

Also read: VBA to Remove Duplicates in Excel

Delete Blank Rows From the Active Worksheet

Suppose you have blank rows in the used range of the currently active worksheet. You can use the below code to delete the blank rows:

Sub DeleteBlankRowsActiveSheet()

    ' Declares variables
    Dim ws As Worksheet
    Dim rng As Range
    Dim row As Long

    ' Sets the active sheet as the target worksheet and defines the used range
    Set ws = ActiveSheet
    Set rng = ws.UsedRange

    ' Loops backwards through the rows in the used range
    For row = rng.Rows.Count To 1 Step -1
        ' Checks if the entire row within the range is blank
        If Application.WorksheetFunction.CountA(rng.Rows(row)) = 0 Then
            ' Deletes the row if it is blank
            rng.Rows(row).Delete
        End If
    Next row

End Sub

The above VBA code is tailored to delete all blank rows within the used range of the active worksheet.

It identifies the used range of the active sheet, which is the area that contains any data, formatting, or formulas. The script then iterates through the rows in this range from the bottom to the top.

For each row, it uses the CountA function to determine if the row is completely empty. If a row is found to be blank, it is deleted.

Also read: Count Rows using VBA in Excel

Delete Blank Rows From All Worksheets of the Current Workbook

Suppose you have blank rows in all worksheets of the current workbook. You can use the following VBA code to delete the blank rows:

Sub DeleteBlankRowsInAllWorksheets()

    ' Declares variables
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    ' Iterates through each worksheet in the current workbook
    For Each ws In ThisWorkbook.Sheets
        ' Finds the last used row in column A of the current worksheet
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

        ' Loops backwards from the last row to the first row
        For i = lastRow To 1 Step -1
            ' Checks if the entire row is blank
            If WorksheetFunction.CountA(ws.Rows(i)) = 0 Then
                ' Deletes the row if it is blank
                ws.Rows(i).Delete
            End If
        Next i
    Next ws

End Sub

The above VBA subroutine is designed to remove all blank rows from every worksheet in the current workbook.

It loops through each worksheet, identifying the last used row in column A for each one.

Then, for each worksheet, it iterates from the bottom row upwards, checking each row for any content.

It uses the CountA function to determine if a row is completely blank (i.e., contains no data). If a row is found to be blank, it is deleted.

This method is effective for cleaning up data ranges by removing unnecessary blank rows across all sheets, thereby streamlining the entire workbook for further processing or analysis.

Also read: VBA Delete Files in Folder

Delete Blank Rows in a Specific Worksheet of Another Open Workbook 

Assuming you have another open workbook named ‘Sales’ with a dataset with blank rows in ‘Sheet1.’ You can use the code below to remove the blank rows:

Sub DeleteBlankRows()

    ' Declares variables
    Dim otherWb As Workbook
    Dim otherWs As Worksheet
    Dim lastRow As Long
    Dim i As Long

    ' Sets references to the other workbook and its specific worksheet
    Set otherWb = Workbooks("Sales.xlsx")
    Set otherWs = otherWb.Sheets("Sheet1")

    ' Finds the last used row in column A of the specified worksheet
    lastRow = otherWs.Cells(otherWs.Rows.Count, "A").End(xlUp).Row

    ' Loops backwards from the last row to the first row
    For i = lastRow To 1 Step -1
        ' Checks if the entire row is blank
        If WorksheetFunction.CountA(otherWs.Rows(i)) = 0 Then
            ' Deletes the row if it is blank
            otherWs.Rows(i).Delete
        End If
    Next i

End Sub

The above VBA code deletes all blank rows from a particular worksheet (“Sheet1”) in another workbook (“Sales.xlsx”).

The script first sets references to the target workbook and worksheet. It then determines the last used row in column A of the specified worksheet.

Following this, it iterates through the rows of the worksheet from the last row upwards. For each row, it checks for any content using the CountA function. If a row is completely empty, it is deleted.

Also read: VBA to Delete Rows Based On Cell Value

Delete Blank Rows in All Worksheets Of Another Open Workbook

Assuming you have another open workbook named ‘Student Population.xlsx’ with blank rows in all worksheets. You can use the code below to remove the blank rows:

Sub DeleteBlankRows()

    ' Declares variables
    Dim otherWb As Workbook
    Dim otherWs As Worksheet
    Dim lastRow As Long
    Dim i As Long

    ' Sets a reference to the external workbook "Student Population.xlsx"
    Set otherWb = Workbooks("Student Population.xlsx")

    ' Iterates through each worksheet in the external workbook
    For Each otherWs In otherWb.Sheets
        ' Determines the last used row in column A of the current worksheet
        lastRow = otherWs.Cells(otherWs.Rows.Count, "A").End(xlUp).Row

        ' Loops backwards from the last row to the first row
        For i = lastRow To 1 Step -1
            ' Checks if the entire row is blank
            If WorksheetFunction.CountA(otherWs.Rows(i)) = 0 Then
                ' Deletes the row if it is blank
                otherWs.Rows(i).Delete
            End If
        Next i
    Next otherWs

End Sub

This subroutine loops through each worksheet in another open workbook. For each worksheet, it finds the last used row in column A and deletes blank rows using the same code as Example #1.

Also read: VBA to Freeze Top Row in Excel

Delete Blank Rows In All Worksheets In All Open Workbooks

Suppose you have blank rows in all worksheets of all open workbooks. You can use the code below to delete the blank rows:

Sub DeleteBlankRowsInAllOpenWorkbooks()

    ' Declares variables
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    ' Iterates through each open workbook
    For Each wb In Workbooks
        ' Iterates through each worksheet in the current workbook
        For Each ws In wb.Sheets
            ' Finds the last used row in column A of the current worksheet
            lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

            ' Loops backwards from the last row to the first row
            For i = lastRow To 1 Step -1
                ' Checks if the entire row is blank
                If WorksheetFunction.CountA(ws.Rows(i)) = 0 Then
                    ' Deletes the row if it is blank
                    ws.Rows(i).Delete
                End If
            Next i
        Next ws
    Next wb

End Sub

The above VBA code deletes all blank rows from every worksheet in all open Excel workbooks.

It loops through each open workbook and, within each workbook, iterates through every worksheet.

For each worksheet, the VB script identifies the last row that contains data in column A. It then examines each row from the bottom upwards, using the CountA function to check if a row is entirely blank.

If a row is found to be empty, it is deleted.

Also read: VBA to Hide Rows Based On Cell Values

Delete Blank Rows In a Worksheet of a Closed Workbook

Suppose you have a closed workbook called ‘Sales Report.xlsm’ with blank rows in ‘Sheet1.’ You can delete the blank rows using the following code:

Sub DeleteBlankRowsinClosedWorkbook()

    ' Disables screen updating for efficiency
    Application.ScreenUpdating = False

    ' Declares variables
    Dim closedWb As Workbook
    Dim closedWs As Worksheet
    Dim closedWorksheetName As String
    Dim lastRow As Long
    Dim i As Long
    Dim closedWorkbookPath As String

    ' Sets the path for the closed workbook
    closedWorkbookPath = "C:\Sales Reports\Sales Report.xlsm"

    ' Opens the closed workbook
    Set closedWb = Workbooks.Open(closedWorkbookPath)

    ' Sets the target worksheet name and gets a reference to it
    closedWorksheetName = "Sheet1"
    Set closedWs = closedWb.Sheets(closedWorksheetName)

    ' Determines the last used row in column A of the specified worksheet
    lastRow = closedWs.Cells(closedWs.Rows.Count, "A").End(xlUp).Row

    ' Loops backwards from the last row to the first row
    For i = lastRow To 1 Step -1
        ' Checks if the entire row is blank
        If WorksheetFunction.CountA(closedWs.Rows(i)) = 0 Then
            ' Deletes the row if it is blank
            closedWs.Rows(i).Delete
        End If
    Next i

    ' Closes the workbook and saves changes
    closedWb.Close SaveChanges:=True

    ' Re-enables screen updating
    Application.ScreenUpdating = True

End Sub

This VBA code removes all blank rows from a specific worksheet in a closed workbook.

It operates on a workbook located at “C:\Sales Reports\Sales Report.xlsm”, specifically targeting the “Sheet1” worksheet.

The VB script opens this workbook, identifies the last used row in column A, and then iterates backwards through the rows. It uses the CountA function to check if each row is completely blank.

Blank rows are deleted, and after completing the operation on all rows, the workbook is saved and closed.

Screen updating is disabled during the script’s execution to enhance performance and is re-enabled once the operation is complete.

Also read: VBA to Find Last Row

Delete Blank Rows In All Worksheets of a Closed Workbook

Assuming you have a closed workbook named ‘Regional Sales.xlsx’ with blank rows in all worksheets. You can delete the blank rows using the following code:

Sub DeleteBlankRows()

    ' Disables screen updating to enhance efficiency
    Application.ScreenUpdating = False

    ' Declares variables
    Dim targetWb As Workbook
    Dim targetWs As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim targetWorkbookPath As String

    ' Sets the path for the closed workbook
    targetWorkbookPath = "C:\Sales Reports\Regional Sales.xlsx"

    ' Opens the target workbook
    Set targetWb = Workbooks.Open(targetWorkbookPath)

    ' Iterates through each worksheet in the target workbook
    For Each targetWs In targetWb.Sheets
        ' Finds the last used row in column A of the current worksheet
        lastRow = targetWs.Cells(targetWs.Rows.Count, "A").End(xlUp).Row

        ' Loops backwards from the last row to the first row
        For i = lastRow To 1 Step -1
            ' Checks if the entire row is blank
            If WorksheetFunction.CountA(targetWs.Rows(i)) = 0 Then
                ' Deletes the row if it is blank
                targetWs.Rows(i).Delete
            End If
        Next i
    Next targetWs

    ' Closes the workbook and saves changes
    targetWb.Close SaveChanges:=True

    ' Re-enables screen updating
    Application.ScreenUpdating = True

End Sub

The above VBA code deletes all blank rows from every worksheet in a specific closed workbook, located at “C:\Sales Reports\Regional Sales.xlsx”.

The script opens the workbook and loops through each of its worksheets. For each worksheet, it identifies the last row with data in column A and then iterates backward through the rows.

Using the CountA function, it checks whether each row is entirely blank. If a row is found to be empty, it is deleted. After processing all worksheets, the workbook is saved and closed.

Delete Blank Rows In All Worksheets In All Closed Workbooks in a Specific Folder

Suppose you have several closed workbooks in a folder named ‘Sales Reports’ on drive C, and you want to remove blank rows in all worksheets in all the closed workbooks. You can use the code below to accomplish the task:

Sub DeleteBlankRowsInClosedWorkbooks()

    ' Disables screen updating for efficiency
    Application.ScreenUpdating = False

    ' Declares variables
    Dim folderPath As String
    Dim fileName As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    ' Sets the path of the folder containing the workbooks
    folderPath = "C:\Sales Reports\"

    ' Retrieves the first Excel file in the folder
    fileName = Dir(folderPath & "*.xls*")

    ' Loops through all Excel files in the folder
    Do While fileName <> ""
        ' Opens the current workbook
        Set wb = Workbooks.Open(folderPath & fileName)

        ' Iterates through each worksheet in the workbook
        For Each ws In wb.Sheets
            ' Finds the last used row in column A of the current worksheet
            lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

            ' Loops backwards from the last row to the first row
            For i = lastRow To 1 Step -1
                ' Checks if the entire row is blank
                If WorksheetFunction.CountA(ws.Rows(i)) = 0 Then
                    ' Deletes the row if it is blank
                    ws.Rows(i).Delete
                End If
            Next i
        Next ws

        ' Closes the workbook and saves changes
        wb.Close SaveChanges:=True

        ' Moves to the next file in the folder
        fileName = Dir
    Loop

    ' Re-enables screen updating
    Application.ScreenUpdating = True

End Sub

The above VBA subroutine cleans up multiple closed workbooks in a specified folder (“C:\Sales Reports”) by deleting all blank rows in each workbook.

It first disables screen updating for efficiency. The VBA script uses the Dir function to loop through all Excel files (.xls, .xlsx, .xlsm, etc.) in the folder.

For each workbook, it opens the file, then iterates through each worksheet. Within each worksheet, the script determines the last row with data in column A and then loops backward from this row, checking for blank rows using the CountA function. Blank rows are deleted.

After processing all sheets in a workbook, the workbook is saved and closed. The loop continues until all Excel files in the folder have been processed. Finally, screen updating is re-enabled.

This method is effective for batch processing multiple workbooks, especially for data cleaning and organization purposes.

Delete Blank Rows From an Excel Table

Suppose you have blank rows in the table named ‘Electronics’ on ‘Sheet1’ of the current workbook. You can use the following code to delete the blank rows:

Sub DeleteBlankRowsTable()

    ' Declares variables
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rng As Range
    Dim i As Long

    ' Sets the worksheet and table object
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set tbl = ws.ListObjects("Electronics")

    ' Loops backwards through the rows of the table
    For i = tbl.DataBodyRange.Rows.Count To 1 Step -1
        ' Sets the range as the current row in the table
        Set rng = tbl.DataBodyRange.Rows(i)

        ' Checks if the row is blank
        If Application.CountA(rng) = 0 Then
            ' Deletes the blank row
            rng.Delete
        End If
    Next i

End Sub

The above VBA code removes blank rows from a specific table named “Electronics” in the “Sheet1” of the current workbook.

It starts by setting references to the worksheet and the table. The script then iterates backwards through the rows of the table’s data body range.

For each row, it checks if the row is completely blank using the CountA function. If a row is found to be empty, it is deleted.

This method is effective for cleaning up tables by removing unnecessary blank rows, which helps in maintaining the integrity and readability of the table data.

The approach of iterating backwards is important to ensure that the row indexing does not get disrupted when rows are deleted.

In this tutorial, I showed you some examples of deleting blank rows in Excel using VBA. I hope you found the tutorial helpful.

Other Excel VBA articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.