VBA Counting blank cells in multiple ranges

[hatstand]

Hi,

I have written this code to find blank cells in a range. It works fine with one or two ranges but not for three or more. Any more than two gives a Complie error.

Any Ideas?

Sub CountBlank()
MsgBox WorksheetFunction.CountBlank(Range("TheRange1", "TheRange2", "TheRange3"))
End Sub

 

[Richard Schollar]

You need to write it like this:

MsgBox WorksheetFunction.CountBlank(Range("TheRange1,TheRange2,TheRange3"))

 

[hatstand]

Thanks for the reply. But I'm getting a run time error. Using that line.

 

[Richard Schollar]

Hmm, should have tested that shouldn't I? It seems COUNTBLANK works differently from the SUM and COUNT versions of the worksheetfunction in that it won't accept multiple ranges.

Incidentally, when you use the form you were with two ranges ie Range("TheRange1","TheRange2") then they define a single rectangular range from the first cell of TheRange1 to the last cell of TheRange2.

 

[hatstand]

I have ended up using the code below, due to the limit of two ranges per line. I really don't know why there is a two limit. I only have eight ranges to add so it's not to big.

Thanks for taking the time to help. Strange that two limit?

Sub test()

Dim TheRange01 As Long
Dim TheRange02 As Long

TheRange01 = WorksheetFunction.CountBlank(Range("TheRange1", "TheRange2"))
TheRange02 = WorksheetFunction.CountBlank(Range("TheRange3", "TheRange4"))

MsgBox (TheRange01 + TheRange02)

End Sub