Eliminating null values

hgus393 · Jan 30th 2006

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

**royUK** · Jan 30th 2006

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)

Wigi · Jan 30th 2006

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

hgus393 · Jan 30th 2006

Re: Eliminating null values

Hum,

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

Compile error
Expected =

J88L · Jan 30th 2006

Re: Eliminating null values

I think take the " off of Roy's before the word Range.

hgus393 · Jan 30th 2006

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

Wigi · Jan 30th 2006

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

hgus393 · Jan 30th 2006

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

Eliminating null values

Participate now!

Share