Defining a variable Range for cells with values in them!

Hi:

I appear to be asking a number of questions about definitions of variable ranges.

I have a range which at its maximum goes from a1:q500

I want to select ALL rows that have a value (non blank) in column A. They will be
sequential so that Selection.End(xlDown).Select and then adding to the column to get
to Q would work. The trouble I am having is with the Range(x:y).Select, where i cant
get the variables set up right!

Having tried various ways, I find I am stuck.

Incidentally, this group is amazing! 99.999% of the stupid questions I have asked have
been answered. I am eternally grateful!

Best

John Baker

Set rng = Range("A1").CurrentRegion
rng.Select

should define the area of your data.

if you want to know the last cell

msgbox rng(rng.count).Address

x = rng(rng.count).row
y = rng(rng.count).column

msgbox "x: " & x & ", y: " & y

if you want to refer to an area within the range

rng(10,3) would be C3

rng(1,1) would be A1

Range(x,y) is invalid syntax if x means row and y means column. Cells(x,y)
would be valid.

--
Regards,
Tom Ogilvy

"John Baker" <[email protected]> wrote in message
news:[email protected]...
> Hi:
>
> I appear to be asking a number of questions about definitions of variable
ranges.
>
> I have a range which at its maximum goes from a1:q500
>
> I want to select ALL rows that have a value (non blank) in column A. They
will be
> sequential so that Selection.End(xlDown).Select and then adding to the
column to get
> to Q would work. The trouble I am having is with the
Range(x:y).Select, where i cant
> get the variables set up right!
>
> Having tried various ways, I find I am stuck.
>
> Incidentally, this group is amazing! 99.999% of the stupid questions I
have asked have
> been answered. I am eternally grateful!
>
> Best
>
> John Baker