CODEWITHSUNDEEP
excelvba
TmSmth
TmSmth

Reputation: 452

Run-time error '424' : Object Required checking if dynamic named range is blank

I used the solution on this question Excel VBA Dynamic Named Ranges: How to check if range is blank but it doesn't work for me, i have a Run-time error'424': Ojbect required on the Set rng = [volTarget] line.

Here is the code, i've changed the name in the [] and it works with a non-empty dynamic named range.

Sub Sample()
    Dim rng As range
    Set rng = [volTarget]
    If Application.WorksheetFunction.CountA(rng) = 0 Then _
    MsgBox "Range is blank"
End Sub

Upvotes: 1

Views: 203

Answers (1)

cyboashu
cyboashu

Reputation: 10433

If there's no range named as volTarget, VBA throws #NAME error when you try to evaluate it like this [volTarget].

In your code, you need to handle the scenario when the named range is missing.

One possible approach can be :

 Sub Sample()
   Dim rng As Range

   If Not IsError([volTarget]) Then
        Set rng = [volTarget]
        If Application.WorksheetFunction.CountA(rng) = 0 Then '=> This is redundant.
            MsgBox "Range is blank"
        End If
    Else
        MsgBox "No such range" '==> This is practically your black range as you are using dynamic named range.
    End If
End Sub

Upvotes: 1

Related Questions