Eliminating null values

  • Hello all,
    I am sorry for the general topic of this thread - what is specifically bugging me is the following:


    I am selecting a range by the following:

    Code
    Sub data()
        Range(Selection, Selection.End(xlDown)).Select
    End Sub


    The problem is that sometimes the file that I am running this macro on has null values (ie nothing) which leads to an erroneous selection ie it stops eventhough there is alot more cells with values after the null value. I cannot choose the entire column as the entire range! Does anybody have a clue how to get by this?
    //Robert

    .¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

  • Re: Eliminating null values


    Use xlUp instead, this will find the last cell in the column that contains a valur. Much more efficient eg

    Code
    "Range("a1",Range("65536").End(xlUp)
  • Re: Eliminating null values


    Haven't tested it, but I believe this works:


    Code
    Sub data()
        Range(Selection, Cells(Cells(65536, Selection.Column).End(xlUp).Row, Selection.Column)).Select
    End Sub


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: Eliminating null values


    Hum,


    I get the following messages when trying to use your code:


    Compile error
    Expected =

    .¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

  • Re: Eliminating null values


    Still does not work!

    Code
    Sub Data
    Range("a1",Range("65536")).End(xlUp)
    Sub


    I added a ) as it was complaining about this but running this I get "Invalid Use of Property"
    ?Robert

    .¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

  • Re: Eliminating null values


    Code
    Sub Data()
    Range("A1", Range("A65536").End(xlUp)).Select
    End Sub


    But my code worked for when you don't restrict to column A.


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: Eliminating null values


    :thanx:
    That works a lot better, Smart using the used cell as the starting point, never thought of doing this...The more I learn the more I understand that I don't know much :?
    :thanx:
    Robert

    .¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!