Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamic Range and CountA (03)

    Is it possible to return a value of zero where the dynamic range name contains nothing?

    Example:
    Dynamic range:"MyRange" =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

    Set af = Application.WorksheetFunction
    MsgBox af.CountA(Range("MyRange"))

    The message box will display the count of items listed for anything other than zero. But what happens when there is no items listed in the dynamic range?

    Thanks,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Range and CountA (03)

    If A2 and below are blank, MyRange is not valid. You could use an error handler to display a custom message if the range is not valid.

    PS was <post:=643,607>post 643,607</post:> helpful?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Range and CountA (03)

    Hans,

    Thanks for the reply.

    Yes, your previous post was very helpful. I have not tested the UNC at this time but will shortly.

    Regards,
    John

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Range and CountA (03)

    Hans,

    <hr>If A2 and below are blank, MyRange is not valid. You could use an error handler to display a custom message if the range is not valid<hr>

    I decided to go with a function to determine if the range actually existed. There appears to be a flaw when it comes to dynamic range names; errors out that it does not exist (it really does). The code is as follows:

    Private Function RangeNameExists(astrRangeName As String) As Boolean
    Dim lstrTemp As String

    On Error GoTo ErrorHandler

    lstrTemp = Range(astrRangeName).Name

    RangeNameExists = True

    Exit Function

    ErrorHandler:
    RangeNameExists = False
    End Function

    Would it be possible for you to test?

    Thanks,
    John

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Range and CountA (03)

    The Name property of a Range is not a string, but a Name object, so the line

    lstrTemp = Range(astrRangeName).Name

    will *always* fail. If you want to test whether a named range is valid (not quite the same as testing whether the name exists), you could use

    Function RangeNameValid(astrRangeName As String) As Boolean
    Dim lstrTemp As String
    On Error GoTo ErrorHandler
    lstrTemp = Range(astrRangeName).Address
    RangeNameValid = True
    Exit Function

    ErrorHandler:
    End Function

    Use like this:

    If RangeNameValid("MyRange") Then
    ...
    Else
    ...
    End If

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Range and CountA (03)

    Hans,

    One interesting point is that the code I provided does work with the string statement. This only holds true if the range is defined in one cell only.

    Regards,
    John

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Range and CountA (03)

    Yes, but what's the use of a dynamic range if it can only be one cell?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •