none
empty Range RRS feed

  • Question

  • Hi

    In my application i set up a name (StoreData) to cover cells if there is data in them. For example, Sheet1.range("A3:A5") has data so in my vba i create the name StoreData to refer to this range.

    Later on in the code, I need to check if there is data set up 

    I have tried to check if [StoreData] is equal to empty, or if the address is 'null' (i.e. no address) but to no avail

    How can I check if there data without explictiy refering to range("A3:A5") in the code?

    thanks

    Peter

    Saturday, October 7, 2017 5:36 PM

Answers

All replies

  • How do you set up the name StoreData?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, October 7, 2017 6:35 PM
  • Hello Peter,

    How do you declare the StoreData? Is that a range or array?

    If it is range, you may use "If StoreData Is Nothing Then"

    If it is an array, you could use James' suggestion in your last thread.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 9, 2017 6:34 AM
    Moderator
  • Hi Hans

    here is a sample of the code - does that help?

     NamesSh.Range("X3:X" & gintStoreAnalysisCount + 2).name = "NAMESSH_StoreAnalysis"

    thanks

    Peter

    Monday, October 9, 2017 7:46 AM
  • Hello,

    You are using named range. You may use the following function to check if the named range exists:

    Function checkNamedRange(name As String) As Boolean
    On Error GoTo ErrorHandler
    If Application.WorksheetFunction.CountA(Range(name)) > 0 Then
    checkNamedRange = True
    Else
    ErrorHandler:
    checkNamedRange = False
    End If
    Exit Function
    Error handler:
    checkNamedRange = False
    End Function

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 9, 2017 8:40 AM
    Moderator
  • ok thanks!
    • Marked as answer by py1 Monday, October 9, 2017 10:35 AM
    Monday, October 9, 2017 10:35 AM