Defining a variable Range for cells with values in them!

[John Baker]

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

 

[Tom Ogilvy]

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

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