countblank sintax

[Alberto Ast]

I am trying to count blanks on a large range of cells but I am getting an error

My function is as follows

If WorksheetFunction.CountBlank(Range_
("M5:Q5,J8,O8:Q8,O12:Q12,O14:Q14,O17:Q17,O20:Q20,_
O23:Q23,J26,A30:Q35,A38:Q44,G17,N26:Q26,J23:K23,C46,_
C48,C61,O61:Q61")) > 0 Then

when ever us a set as M5:Q5... those are merged cells... I tried with just
first cell of each merge cell lime just M5 but get same error

Run-time error '1004:
Unsable to get the CountBlank property of the worksheetFunction class

 

[Gary''s Student]

The range shoudl be contiguous:

Sub dural()
Set r = Range("A1:A3")
MsgBox (Application.WorksheetFunction.CountBlank(r))
Set r = Range("A1,A3")
MsgBox (Application.WorksheetFunction.CountBlank(r))
End Sub


The first countblank works, the second fails.

 

[Barb Reinhardt]

You could do something like this


Sub CountAllEmpty()
Dim myRange As Excel.Range
Dim myCount As Long
Dim r As Excel.Range


Set myRange = _
Range("M5:Q5,J8,O8:Q8,O12:Q12,O14:Q14,O17:Q17,O20:Q20," & _
"O23:Q23,J26,A30:Q35,A38:Q44,G17,N26:Q26,J23:K23,C46," & _
"C48 ,C61, O61:Q61")



myCount = 0
For Each r In myRange
If IsEmpty(r) Then
myCount = myCount + 1
End If
Next r
Debug.Print myCount, myRange.Count

End Sub

Maybe make it a function.

HTH,
Barb Reinhardt