How to remove empty rows in Excel



I find myself in a predicament.

Despite the existence of values at B3 and D10, the code
deletes rows
is only applicable to A3 and A10.

My aim is to apply
delete rows
to every sheet within the workbook.

Sub Delete()
Dim xlastrow As Integer
Dim xrow As Integer
xrow = 1
xlastrow = ActiveCell.Row
Do Until xrow = xlastrow
    If Cells(xrow, 1).Value = "" Then
        Cells(xrow, 1).Select
        xrow = xrow - 1
        xlastrow = xlastrow - 1
    End If
    xrow = xrow + 1
End Sub

Solution 1:

If you want to verify every cell in a row, try utilizing function


. Since you’re handling numerous sheets, it’s recommended to establish a


that accepts a worksheet as input and operates on that specific sheet instead of depending on the active sheet. Additionally, it’s best to steer clear of using Select.

To simplify implementation, it is recommended to delete data in reverse order. Therefore, your routine may resemble the following.

Sub DeleteEmptyRows(ws As Worksheet)
    Dim row As Long
    For row = ws.UsedRange.Rows.Count To 1 Step -1
        Dim r As Range
        Set r = ws.Cells(row, 1).EntireRow
        If WorksheetFunction.CountA(r) = 0 Then
        End If
End Sub

Solution 2:

 Dim r As Range
 Dim i As Integer
 i = 3
 Set r = Rows(i)
 MsgBox "Number of cells with value " + Str(WorksheetFunction.CountA(r))

The output will be the text

"Number of cells with value"

, indicating the count of non-empty cells in row 3.

The expected output for a blank row is the code



In case you mark
delete row
as 3, it is important to review row 3 once more as it will include
previous values
of row 4.

Solution 3:

This code will scan the entire length of the rows to detect any numbers or text values and eliminate empty rows. Additionally, the ”
Delete Rows
” statement will only be executed once, leading to enhanced performance.

Option Explicit
Sub removeBlankRows()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Dim xlastrow As Long
    xlastrow = ws.Range("a65000").End(xlUp).Row
    Dim xrow As Long
    For xrow = xlastrow To 1 Step -1
        Dim c As Long
        c = WorksheetFunction.CountA(ws.Rows(xrow))
        Dim d As Long
        d = WorksheetFunction.Count(ws.Rows(xrow))
        If c = 0 And d = 0 Then
            Dim deleteRng As Range
            If deleteRng Is Nothing Then
                Set deleteRng = ws.Cells(xrow, 1)
                Set deleteRng = Union(ws.Cells(xrow, 1), deleteRng)
            End If
        End If
    If Not deleteRng Is Nothing Then deleteRng.EntireRow.Delete
End Sub

Solution 4:

Two things you need:

  1. What is the method to determine if a row is vacant?

To locate the solution, refer to another post that includes the code check



  1. What are the steps to remove items from a
    sorted collection

Sorted collection? I’m not following. To delete rows from first to last, the collection must be sorted. The proper method involves maintaining an index of your location and adjusting it when a row is deleted. If the index is increased and skips over a row that needs to be deleted, it will cause issues. There are numerous explanations available online on how to properly execute this process.

Begin from the bottom row and move towards the top.

Good luck

Frequently Asked Questions