Check a range for empty AND errors cells

[Gringoire]

Dear friends,
As inexperienced user I'm facing with another issue:

I need to check if a Range is empty so I used this code:

For j = Row_1dati To Row_lastdati
   If WorksheetFunction.CountA(Range(Cells(j, Col_1dati), Cells(j, Ult_col))) = 0 Then
      Rows(j).Delete
      ...some other code here...
   End If
Next j

This work flawlessly until I check a Row containing a #RIF! or #DIV/0! value. In this case CountA returns a value that is not ZERO
I need to deal with these error values exactly as if the Row is totally empty but I cannot easily make a pre-check about errors because the error value itself is created at runtime due to previous Rows(j).Delete iteration.

So my question is:
Is it possible to check if a Range includes only empty and/or Error cells?

Thanks for your help.

 

[gallen]

Hello, Unless some of my more learned friends know better, I believe the best way would be to check each cell individually instead of as range like you do.

I'd do something like this:

(Untested, please use copy of data)

    Dim c As Range 'used in loop
   
    For j = Row_1dati To Row_lastdati
        For Each c In Range(Cells(j, Col_1dati), Cells(j, Ult_col))
            If IsEmpty(c) = True Or IsError(c) Then c.EntireRow.Delete
        Next c
    Next j

EDIT: As you are deleting rows you may need to start at the bottom row in your loop. If it fails, let me know

 

[Snakehips]

@Gringoire
Maybe as below.

For j =  Row_lastdati to Row_1dati  Step -1. '****** Delete upwards
Set My_Range = Range(Cells(j, Col_1dati), Cells(j, Ult_col))

   If WorksheetFunction.CountA(My_Range) = WorksheetFunction.CountIf(My_Range, "#DIV/0!") + WorksheetFunction.CountIf(My_Range, "#REF!") Then
      Rows(j).Delete
      '...some other code here...
   End If
Next j

Note that as gallen says, best to delete from the bottom up. Otherwise you will miss alternate rows.
@gallen, I think your method may not be suitable as it can delete a whole row based on just one empty cell in the row?

 

[Gringoire]

@Gringoire
Maybe as below.

For j =  Row_lastdati to Row_1dati  Step -1. '****** Delete upwards
Set My_Range = Range(Cells(j, Col_1dati), Cells(j, Ult_col))

   If WorksheetFunction.CountA(My_Range) = WorksheetFunction.CountIf(My_Range, "#DIV/0!") + WorksheetFunction.CountIf(My_Range, "#REF!") Then
      Rows(j).Delete
      '...some other code here...
   End If
Next j

Note that as gallen says, best to delete from the bottom up. Otherwise you will miss alternate rows.
@gallen, I think your method may not be suitable as it can delete a whole row based on just one empty cell in the row?

Thanks Snakehips, I'll try your suggestion tomorrow at office.
To avoid missing rows, I just added a j=j-1 instruction after Rows(j).Delete but I guess your solution is more elegant.