How to remove empty rows in Excel

Feedback


Question:

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
Range("a65000").End(xlUp).Select
xlastrow = ActiveCell.Row
Do Until xrow = xlastrow
    If Cells(xrow, 1).Value = "" Then
        Cells(xrow, 1).Select
        Selection.EntireRow.Delete
        
        xrow = xrow - 1
        xlastrow = xlastrow - 1
    End If
        
    xrow = xrow + 1
Loop
End Sub


Solution 1:

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

CountA

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

Sub

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
            r.Delete
        End If
    Next
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

0

.

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)
            Else
                Set deleteRng = Union(ws.Cells(xrow, 1), deleteRng)
            End If
            
        End If
        
    Next
    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

Application.CountA(SomeCell.EntireRow)=0

.

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

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