Hi Dave,
The blank cells were derived from a sorting of data. When
I clear them, all is ok. I'll use your code and Dave's to
solve my problem. Tricky eh!
Thanks for your help,
Cheers,
Rick

-----Original Message-----
I bet you used to have formulas in that column. Some of
them evaluated to ""
(like =if(b23,b2,""))

When you copied|paste special, those cells that "" in
them aren't empty. And
that's why .end(xldown) and End|Down arrow from the
worksheet don't work the way
you think it should.

Depending on your data, you could do:

with activesheet.range("a:a")
.value = .value
end with

This converts all the formulas to values and cleans up
that "junk".

But if you had formulas in that range, they'd be gone.



Rick wrote:

Hi,
How do I write a macro to select a variable cell range
so
that I can copy the block of data elsewhere.
e.g. My data range is A3:A18
Sometimes I have data in cells A3:A6 or A3:A10 etc. It
varies.

I only wish to select the block that contains data,
eleiminating the blank cells. End down takes me to the
row
A18, regardless whether or not some of the cells have
data
in them.

Could anyone assist please,
Thanks

--

Dave Peterson

.