IF entire range blank THEN ...

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

I end a macro by enableing the AutoFilter so I can stop and check for my
ERROR message. If there is no cell with my error value, then I proceed to
the next step. It has just dawned on me that I can probably code this so
that
IF there is no value in any cell in B2:B700 THEN do this ELSE exit sub

I tried writing
' B2:B7000 is my range; C2 is an empty cell
IF (COUNTIF($B$2:$B$7000,C2)=0 THEN
' next routine
ELSE Exit Sub

but it keeps telling me $ is an invalid character. What am I missing?

Ed
 
The syntax would be:
IF Application.COUNTIf(Range("B2:B700"),Range("C2"))=0 THEN
' next routine
ELSE
Exit Sub
End if

but I would use CountA

IF Application.COUNTA(Range("B2:B700"))=0 THEN
' next routine
ELSE
Exit Sub
End if
 
Thank you, Tom. It works great!

As for the $ as an invalid character, is that just the dirfference between
writing a formula, and writing VBA? In other words, I shouldn't assume that
just because a formula in a worksheet is good, that's what VBA will
recognize in the same way?

Ed
 
In VBA, you need to pass a range object when a range is need in a formula.
In a worksheet, the functions understand A1:B9 refers to a range - in VBA
you have to use a range object.
 
Thanks, Tom. I appreciate the info.

Ed


Tom Ogilvy said:
In VBA, you need to pass a range object when a range is need in a formula.
In a worksheet, the functions understand A1:B9 refers to a range - in VBA
you have to use a range object.
Click to expand...
 
Back
Top