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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

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,454
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,122,747
Messages
5,597,905
Members
414,189
Latest member
Duque_

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