Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with data not getting plotted | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Charts and Charting in Excel | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Charting data ranges that change | Charts and Charting in Excel | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) |