Check for non-blanks

[agibsonsw]

Hello. Excel 2003 VBA.

Is there a way that I can check quickly whether a range has no blank cells?

Thanks, Andy.

[HansV]

If by "blank" you mean really blank, i.e. not even a formula returning an empty string "", you could use the COUNTBLANK worksheet function:

Code: Select all

If Application.CountBlank(Range("A1:B10")) = 0 Then
  ' no blanks
Else
  ' blanks
End If

If you also need to take formulas returning "" into account, you can use the COUNTIF worksheet function:

Code: Select all

If Application.CountIf(Range("A1:B10"),"") = 0 Then
  ' no blanks
Else
  ' blanks
End If

[agibsonsw]

Hi.
It occurred to me to use SpecialCells(xlCellTypeBlanks) and allow this to generate an error,
but your method is better (as usual :)). Andy.