Identify missing formulas in a column - Union of ranges issue.

  • I had a problem today where I needed to indentify missing formulas from a column of data. Lets assume the data is in A1:A10.


    I thought it was a simple problem using specialcells method to identify constants (i.e. not a formula) and also blanks. This was fine, but I realised quickly that UNION will fail if any of the ranges were "nothing" i.e. the range was missing either a constant or a blank. So below are the three options. The last two worked, but arent ideal.



    Are there any other simpler suggestions (without loops anyway) to find missing formulas from a column of data.


    Regards
    Ger

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Hi Ger,
    I don't fully follow but,,


    http://www.ozgrid.com/VBA/special-cells.htm

  • Thanks Pike - that works... its a better option than my multiple if statements, and complicated IIF effort.


    To restate the problem - if any of the ranges in a union is nothing then the resulting union is also nothing. So for example.
    Union ("A1", "E10") results in a range("A1","10")
    Union ("A1", "E10","G20") results in a range("A1","10","G20")
    However,
    Union ("A1", nothing ,"G20") results in a range = nothing (as opposed to a range with "A1" and "G20").


    So in my case, I had to test the ranges before "unioninising" them :-)


    its a bit of a limitation on Union that I wasnt aware of... and it kinda sucks if you are doing a union with many ranges.


    Ger

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________