Test for empty range and delete row

FredMFoley

Board Regular
Joined
Mar 18, 2002
Messages
58
How do you test a range to see if it is empty (ie every cell in it = ""), without testing each cell individually.

I want to delete all rows in which the range AD to AR is empty.

Thanks
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

One way is to use the COUNTA function.

=counta(ad4:ar4).

If all the cells are blank, then result will be 0. If >0, then there is something in one of the cells.


HTH

Tony
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
To use CountA in some code you could do something like this:
Code:
Sub DeleteDemo()
Dim cel As Range, TestRng As Range, RowsToDelete As Range
Dim LstRw As Long
LstRw = Cells(Rows.Count, "AR").End(xlUp).Row
Set TestRng = Range(Cells(1, "AD"), Cells(LstRw, "AD"))
    
    For Each cel In TestRng
        If Application.WorksheetFunction.CountA(Range(Cells(cel.Row, 30), Cells(cel.Row, 44))) = 0 Then
            If RowsToDelete Is Nothing Then
                Set RowsToDelete = cel
              Else
                Set RowsToDelete = Union(cel, RowsToDelete)
            End If
        End If
    Next cel
    
If Not RowsToDelete Is Nothing Then RowsToDelete.EntireRow.Delete

End Sub
Note that this will not delete any row(s) that contain any formula that is returning as a blank. (For that is not a truly blank row then.)

Hope it helps.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,291
Messages
5,571,334
Members
412,382
Latest member
Langtn02
Top