Setting a variable cell address

[Hugh Jago]

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 Phillips]

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

--

HTH

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

 

[Hugh Jago]

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]

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)