Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How do you check a range is empty (97sr2)

    Hi all,

    My mind is foggy, and I need some advice.

    I need to test that a range is empty (no values, formulas, etc) before I paste data to the range.
    What is the best way to do this? Currently I am using

    Function IsRangeEmpty(RngIn As Range) As Boolean
    IsRangeEmpty = Application.CountA(RngIn) = 0
    End Function

    However I am unsure if this will detect all possible types of entries to the range.

    Any advice would be appreciated.

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

    Re: How do you check a range is empty (97sr2)

    If you want to check for empty contents, this should work fine. Even an apostrophe in a cell, which merely marks it as text, causes the function to return False. It won't detect the presence of formattng, conditional formatting or data validation.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do you check a range is empty (97sr2)

    Thanks for the fast & clear answer.

Posting Permissions

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