Test for empty range and delete row

[FredMFoley]

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

 

[acw]

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

 

[FredMFoley]

Thanks Tony

Much easier than the way I was trying to do it.

 

[HalfAce]

To use CountA in some code you could do something like this:

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.

 

[FredMFoley]

Thanks HalfAce
Your code does exactly what I wanted.

 

[HalfAce]

Most welcome. Glad it helped.