Setting a variable cell address
 

I'm trying to run a macro.
The data can change in row length from spreadsheet to spreadsheet.
I want to find the end of the data (i.e row 20 or row 1000)
and in the next row set up various arrays.

Obviously the 'shft' + 'ctrl' down arrow takes me to the end bottom of the
data, but the macro then sets absolute address so the next spreadsheet gets
the wrong parameters.

Any ideas?

Thanks

cLastRow = Cells(Rows.Count,"A").End(xlUp).Row

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hugh Jago" wrote in message
...
I'm trying to run a macro.
The data can change in row length from spreadsheet to spreadsheet.
I want to find the end of the data (i.e row 20 or row 1000)
and in the next row set up various arrays.

Obviously the 'shft' + 'ctrl' down arrow takes me to the end bottom of the
data, but the macro then sets absolute address so the next spreadsheet
gets
the wrong parameters.

Any ideas?

Thanks

Bob,
Thanks for the reply, but for the life of me I can't get this to work.
As an example if I start with Data from A2 to A6 then I need to go to A2,
count down, and in cell A8 I want to bring in additional calculations.
The next time I may have data from A2 to A10, so the additional calculations
will be pasted into A12.
Your thoughts greatly appreciated.

Hugh
"Bob Phillips" wrote in message
...

cLastRow = Cells(Rows.Count,"A").End(xlUp).Row

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hugh Jago" wrote in message
...
I'm trying to run a macro.
The data can change in row length from spreadsheet to spreadsheet.
I want to find the end of the data (i.e row 20 or row 1000)
and in the next row set up various arrays.

Obviously the 'shft' + 'ctrl' down arrow takes me to the end bottom of
the
data, but the macro then sets absolute address so the next spreadsheet
gets
the wrong parameters.

Any ideas?

Thanks

Hugh,

If you debug this, you will see that cLastRow will return 6. To then access
A8, you would use something like

Cells(cLastRow+2,"A").Value = ...

Next time, cLastRow will return 8, etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hugh Jago" wrote in message
...
Bob,
Thanks for the reply, but for the life of me I can't get this to work.
As an example if I start with Data from A2 to A6 then I need to go to A2,
count down, and in cell A8 I want to bring in additional calculations.
The next time I may have data from A2 to A10, so the additional
calculations
will be pasted into A12.
Your thoughts greatly appreciated.

Hugh
"Bob Phillips" wrote in message
...

cLastRow = Cells(Rows.Count,"A").End(xlUp).Row

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hugh Jago" wrote in message
...
I'm trying to run a macro.
The data can change in row length from spreadsheet to spreadsheet.
I want to find the end of the data (i.e row 20 or row 1000)
and in the next row set up various arrays.

Obviously the 'shft' + 'ctrl' down arrow takes me to the end bottom of
the
data, but the macro then sets absolute address so the next spreadsheet
gets
the wrong parameters.

Any ideas?

Thanks