Check a range for empty AND errors cells

Gringoire

New Member
Joined
Nov 18, 2016
Messages
29
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:
VBA 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.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,006
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)

VBA Code:
    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

Well-known Member
Joined
May 17, 2009
Messages
5,121
Office Version
  1. 2013
Platform
  1. Windows
@Gringoire
Maybe as below.

VBA Code:
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

New Member
Joined
Nov 18, 2016
Messages
29
@Gringoire
Maybe as below.

VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,105
Members
416,161
Latest member
David1966Lewis

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