End of data
 

I have a monthly spreadsheet to which I add 100-120 data entries per day to
columns A to G. I have helper columns H to N which follow the format
=if(a1="","",formula). I have added these helper columns out to row 4000 to
ensure that there are sufficient to cater for a full month of data.

I add the data for the current day and then run a macro which sorts the data
based on helper columns that does the sort and then places the cursor in cell
G1. This works beautifully.

I then do some things using the data.

The last thing I do is run a second macro, which sorts the data back into date
order (column A), and then save the spreadsheet. I have a small problem with
this macro in that I haven't figured out how to place the cursor at the end of
data in column A. <CTRL End places it out at row 4000 which is the end of the
helper columns. The problem is that the data in column A advances by 100-120
entries each day so I can't use a fixed row position - i.e. A1000.

At the moment I am resorting to using the Page Down key but that gets tiresome
as it gets towards the end of month.

Any ideas?

--

Cheers . . . JC

Hi JC

If you need to select the last A cell:

Cells(Rows.Count, 1).End(xlUp).Select

If you just need to know which row it's in without leaving your current
position:

Dim LastARow As Long
LastARow = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox LastARow

And you may find this useful perhaps:

Range("A2").CurrentRegion.Select

it selects all data around cell A2, no matter how wide or long list that is.

HTH. Best wishes Harald

"JC" skrev i melding
...
I have a monthly spreadsheet to which I add 100-120 data entries per day
to
columns A to G. I have helper columns H to N which follow the format
=if(a1="","",formula). I have added these helper columns out to row 4000
to
ensure that there are sufficient to cater for a full month of data.

I add the data for the current day and then run a macro which sorts the
data
based on helper columns that does the sort and then places the cursor in
cell
G1. This works beautifully.

I then do some things using the data.

The last thing I do is run a second macro, which sorts the data back into
date
order (column A), and then save the spreadsheet. I have a small problem
with
this macro in that I haven't figured out how to place the cursor at the
end of
data in column A. <CTRL End places it out at row 4000 which is the end
of the
helper columns. The problem is that the data in column A advances by
100-120
entries each day so I can't use a fixed row position - i.e. A1000.

At the moment I am resorting to using the Page Down key but that gets
tiresome
as it gets towards the end of month.

Any ideas?

--

Cheers . . . JC

Hi Harald,

Thanks. That worked a treat.

JC

On Sat, 19 Feb 2005 22:44:25 +0100, "Harald Staff"
wrote:

Hi JC

If you need to select the last A cell:

Cells(Rows.Count, 1).End(xlUp).Select

If you just need to know which row it's in without leaving your current
position:

Dim LastARow As Long
LastARow = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox LastARow

And you may find this useful perhaps:

Range("A2").CurrentRegion.Select

it selects all data around cell A2, no matter how wide or long list that is.

HTH. Best wishes Harald

"JC" skrev i melding
...
I have a monthly spreadsheet to which I add 100-120 data entries per day
to
columns A to G. I have helper columns H to N which follow the format
=if(a1="","",formula). I have added these helper columns out to row 4000
to
ensure that there are sufficient to cater for a full month of data.

I add the data for the current day and then run a macro which sorts the
data
based on helper columns that does the sort and then places the cursor in
cell
G1. This works beautifully.

I then do some things using the data.

The last thing I do is run a second macro, which sorts the data back into
date
order (column A), and then save the spreadsheet. I have a small problem
with
this macro in that I haven't figured out how to place the cursor at the
end of
data in column A. <CTRL End places it out at row 4000 which is the end
of the
helper columns. The problem is that the data in column A advances by
100-120
entries each day so I can't use a fixed row position - i.e. A1000.

At the moment I am resorting to using the Page Down key but that gets
tiresome
as it gets towards the end of month.

Any ideas?

--

Cheers . . . JC