Using a variable or object to reference a range 2

[Stevo911]

Hi

My problem:
I need to manipulate different worksheets of varying lengths and it is not convenient to scroll through my macro code and change the ranges.

Is it possible to set up an input box, eg.

Length = Application.InputBox("Insert the rumber of rows.", "Query", , , , , , 1)

And then using this variable (Length) for your range instead of, for example, AF2:AF10900.

So then it would be possble to use something like:
Range("Length").Select, instead of

Range("AF2:AF10900").Select

I've tried many variations of: Range("Length").Select, but i can't get it to work.

Please help. Thanks

 

[macropod]

Hi Stevo,

Since your inputbox is only getting the number of rows, you'd ned to use something like:
Range("AF2:AF" & Length).Select

Cheers

 

[TonyJollans]

Hi Stevo,

Can you give a few more details as it may be possible for your code to automatically detect the range required without having to prompt the user at all. What are the conditions which let the user know that he wants to work with 10,900 (or whatever) rows?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[fredericofonseca]

with ranges you can do as follows also.
.Range(Cells(my_row1, my_col1), Cells(my_row2, my_Col2))

where the my_* are integers.

my_row1=1
my_col1=1
my_row2 = objexcelwks.Cells.SpecialCells(xlLastCell).Row
my_col2 = objexcelwks.Cells.SpecialCells(xlLastCell).Column
objexcelwks.Range(Cells(row1, _col1), Cells(_row2, my_col2)).Select

would select all used cells on the worksheet objexcelwks



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Stevo911]

Thanks guys!
Problem solved