[Excel VBA] How to Remove Empty Rows from a Table

2025年12月29日

This article explains how to efficiently delete empty rows from a table using VBA.

Prerequisites

This code is designed for the following conditions:

Table Structure: The table exists on a single worksheet.
Target Range: The rows to be deleted are within a specific range (e.g., rows 19 to 25).
Condition: Only rows that are completely empty will be deleted.
Environment: VBA is enabled and macros are allowed to run.

VBA Code to Delete Empty Rows

Use the following code to delete completely empty rows within a specified range.

Sub DeleteEmptyRows()
    Dim outputSheet As Worksheet
    Dim row As Long
    Dim startRow As Long
    Dim endRow As Long

    ' Set the target sheet
    ' Change "TargetSheetName" to your actual sheet name
    Set outputSheet = ThisWorkbook.Sheets("TargetSheetName")

    ' Specify the start and end rows of the target range
    startRow = 19 ' Start row
    endRow = 25   ' End row

    ' Loop from the bottom to the top to delete empty rows
    For row = endRow To startRow Step -1
        If Application.WorksheetFunction.CountA(outputSheet.Rows(row)) = 0 Then
            outputSheet.Rows(row).Delete
        End If
    Next row
End Sub

Code Explanation

1. Set the Target Sheet

Set outputSheet = ThisWorkbook.Sheets("TargetSheetName")

Specify the worksheet containing the table you want to clean. Be sure to change "TargetSheetName" to the actual name of your sheet.

2. Set the Deletion Range

startRow = 19
endRow = 25

Define the range of rows to be checked. In this example, rows 19 through 25 are targeted.

3. Check for Empty Rows

If Application.WorksheetFunction.CountA(outputSheet.Rows(row)) = 0 Then

The CountA function checks if the row contains any data. If the result is 0, the row is considered completely empty.

4. Delete the Empty Row

outputSheet.Rows(row).Delete

This deletes the empty row. The code processes rows from the bottom to the top (Step -1). This is crucial because deleting a row shifts the remaining rows up; looping backwards prevents the row index numbers from becoming incorrect.

Summary

Using this VBA code allows you to efficiently remove empty rows from your tables. This is particularly useful for data cleaning tasks where blank lines need to be removed to prepare data for analysis.