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

If you are always starting on A3 and the data is all continuous (no
blank cells between values), then this should work for you. I used
the macro recorder to get it.

Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Random

On Sun, 17 Aug 2003 06:03:56 -0700, "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

When you say blank, do you mean empty or is there a formula in there
returning an empty string. If the cell is truly empty, I don't
understand why it wouldn't work. Just for kicks, try selecting and
deleting the first "blank" cell below your filled cells and then run
the macro again. If that works, it should be able to point one of the
more experienced programmers here in the right direction.



Random


On Sun, 17 Aug 2003 06:35:32 -0700, "Rick"
wrote:

Thanks Random,
Tried that, but no go. I've done the same thing with macro
recorder but same result. It just selects A3:A18......the
whole block. Always start on A3 and data is all
continuous. I just want the data only. Back to square one.
Very frustrating!
Cheers


-----Original Message-----
If you are always starting on A3 and the data is all
continuous (no
blank cells between values), then this should work for
you. I used
the macro recorder to get it.

Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Random

On Sun, 17 Aug 2003 06:03:56 -0700, "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

.

Hi Random,
You are right. Even though the cells were blank they were
derived from a sorting of data. When I clear them and run
your code presto! So I'll incorporate Dave's suggestion
and yours and my problem is solved.
Thanks again.


-----Original Message-----
When you say blank, do you mean empty or is there a
formula in there
returning an empty string. If the cell is truly empty, I
don't
understand why it wouldn't work. Just for kicks, try
selecting and
deleting the first "blank" cell below your filled cells
and then run
the macro again. If that works, it should be able to
point one of the
more experienced programmers here in the right direction.



Random


On Sun, 17 Aug 2003 06:35:32 -0700, "Rick"

wrote:

Thanks Random,
Tried that, but no go. I've done the same thing with
macro
recorder but same result. It just selects
A3:A18......the
whole block. Always start on A3 and data is all
continuous. I just want the data only. Back to square
one.
Very frustrating!
Cheers


-----Original Message-----
If you are always starting on A3 and the data is all
continuous (no
blank cells between values), then this should work for
you. I used
the macro recorder to get it.

Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Random

On Sun, 17 Aug 2003 06:03:56 -0700, "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

.


.

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

.