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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 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,456
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,857
Messages
5,834,008
Members
430,251
Latest member
fscrn_ffg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top