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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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
Back
Top