Hello. Excel 2003 VBA.
Is there a way that I can check quickly whether a range has no blank cells?
Thanks, Andy.
Check for non-blanks
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Check for non-blanks
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78533
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Check for non-blanks
If by "blank" you mean really blank, i.e. not even a formula returning an empty string "", you could use the COUNTBLANK worksheet function:
If you also need to take formulas returning "" into account, you can use the COUNTIF worksheet function:
Code: Select all
If Application.CountBlank(Range("A1:B10")) = 0 Then
' no blanks
Else
' blanks
End If
Code: Select all
If Application.CountIf(Range("A1:B10"),"") = 0 Then
' no blanks
Else
' blanks
End If
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Check for non-blanks
Hi.
It occurred to me to use SpecialCells(xlCellTypeBlanks) and allow this to generate an error,
but your method is better (as usual :)). Andy.
It occurred to me to use SpecialCells(xlCellTypeBlanks) and allow this to generate an error,
but your method is better (as usual :)). Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.