none
Checking if an array is empty RRS feed

  • Question

  • Hi

    I have code like this:

    Dim varTable as Variant

    varTable = Sheet1.Range("B2").CurrentRegion

    Sometimes there will be data in Range("B2") and adjacent cells - which will then be 'loaded' into varTable.

    Other times there will be no data in Range("B2") so varTable will be empty.

    The following code works if varTable is empty

    If varTable = empty then

       msgbox "empty"

    else

      msgbox "not empty"

    end if

    If there is data in varTable , however, it fails with a type mismatch.

    I am looking for a way to check if there is data in the table. I suppose I could check if there is data in Range("B2") at the outset , but wondered if there was a better or more elegant way.

    thanks,

    Peter

     

    Tuesday, September 12, 2017 1:49 PM

Answers

  • Instead of

            If varTable = empty then

    use

            If IsEmpty(varTable) Then


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

    • Marked as answer by py1 Tuesday, September 12, 2017 3:15 PM
    Tuesday, September 12, 2017 3:03 PM

All replies

  • Instead of

            If varTable = empty then

    use

            If IsEmpty(varTable) Then


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

    • Marked as answer by py1 Tuesday, September 12, 2017 3:15 PM
    Tuesday, September 12, 2017 3:03 PM
  • Ah! I should have tought of that!

    thanks Hans!

    all the best Peter

    Tuesday, September 12, 2017 3:15 PM