Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macro - next down
We have a model with a macro to automate the process.
The number of rows of data from both lots of input varies each time. We need to paste the second lot of data below the first lot. Does anyone know how to select the next cell boelow the last one on the first paste? When we record end down, down the macro uses an absolute cell reference and this stops the rest of the macro from working properly. We've tried find next blank and this goes to the next column. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macro - next down
Hi Julie
dim lastrow as long, nextcell as string LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row nextcell="A" & lastrow+1 -- Regards Roger Govier "julie cooke" <julie wrote in message ... We have a model with a macro to automate the process. The number of rows of data from both lots of input varies each time. We need to paste the second lot of data below the first lot. Does anyone know how to select the next cell boelow the last one on the first paste? When we record end down, down the macro uses an absolute cell reference and this stops the rest of the macro from working properly. We've tried find next blank and this goes to the next column. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macro - next down
I found the answer on the programming bit of this site nad applied :
Selection.End(xlDown).Select (was the bit we aready had) ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate Which gives the next row (or column) "Roger Govier" wrote: Hi Julie dim lastrow as long, nextcell as string LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row nextcell="A" & lastrow+1 -- Regards Roger Govier "julie cooke" <julie wrote in message ... We have a model with a macro to automate the process. The number of rows of data from both lots of input varies each time. We need to paste the second lot of data below the first lot. Does anyone know how to select the next cell boelow the last one on the first paste? When we record end down, down the macro uses an absolute cell reference and this stops the rest of the macro from working properly. We've tried find next blank and this goes to the next column. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macro - next down
Julie
Be warned that xldown will stop at the first blank cell, which in a lot of cases will not be last used cell in a column. Best to use Roger's suggestion of going to bottom of sheet and working up to last row. Gord Dibben MS Excel MVP On Thu, 5 Oct 2006 07:29:02 -0700, julie cooke wrote: I found the answer on the programming bit of this site nad applied : Selection.End(xlDown).Select (was the bit we aready had) ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate Which gives the next row (or column) "Roger Govier" wrote: Hi Julie dim lastrow as long, nextcell as string LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row nextcell="A" & lastrow+1 -- Regards Roger Govier "julie cooke" <julie wrote in message ... We have a model with a macro to automate the process. The number of rows of data from both lots of input varies each time. We need to paste the second lot of data below the first lot. Does anyone know how to select the next cell boelow the last one on the first paste? When we record end down, down the macro uses an absolute cell reference and this stops the rest of the macro from working properly. We've tried find next blank and this goes to the next column. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macro - next down
The first response doesn't work and is too complex.
We can already verify that the column in question always has data in all rows. If this is not always the case you can use end home and then home. This will take you to the bottom of the data in column A. You can then move right or left or up or down using my (now found) solution (for left and up use -). by the appropriate number of columns rows. Also end down takes you to the row ABOVE the first blank cell, not the first blank cell. So that doesn't go to the correct place. "Gord Dibben" wrote: Julie Be warned that xldown will stop at the first blank cell, which in a lot of cases will not be last used cell in a column. Best to use Roger's suggestion of going to bottom of sheet and working up to last row. Gord Dibben MS Excel MVP On Thu, 5 Oct 2006 07:29:02 -0700, julie cooke wrote: I found the answer on the programming bit of this site nad applied : Selection.End(xlDown).Select (was the bit we aready had) ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate Which gives the next row (or column) "Roger Govier" wrote: Hi Julie dim lastrow as long, nextcell as string LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row nextcell="A" & lastrow+1 -- Regards Roger Govier "julie cooke" <julie wrote in message ... We have a model with a macro to automate the process. The number of rows of data from both lots of input varies each time. We need to paste the second lot of data below the first lot. Does anyone know how to select the next cell boelow the last one on the first paste? When we record end down, down the macro uses an absolute cell reference and this stops the rest of the macro from working properly. We've tried find next blank and this goes to the next column. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macro - next down
Hi Julie
The first response doesn't work and is too complex. Obviously it is your prerogative as to whether you accept any advice offered freely and in all good faith. too complex Again, that is a matter of your opinion doesn't work here, I beg to differ. It works. Also end down takes you to the row ABOVE the first blank cell, not the first blank cell. So that doesn't go to the correct place Again, Gord is very well aware of this, and was not suggesting that as a solution. Had he been doing so, he would have said you needed to offset by one row (as did I). You had already said that your solution was taking a cell offset by 1 row and 0 columns. He was merely pointing out that IF there were gaps in the data, such a method COULD result in the wrong position. In your particular case your solution works, Gord was suggesting that the solution offered by me, works in every case. -- Regards Roger Govier "julie cooke" wrote in message ... The first response doesn't work and is too complex. We can already verify that the column in question always has data in all rows. If this is not always the case you can use end home and then home. This will take you to the bottom of the data in column A. You can then move right or left or up or down using my (now found) solution (for left and up use -). by the appropriate number of columns rows. Also end down takes you to the row ABOVE the first blank cell, not the first blank cell. So that doesn't go to the correct place. "Gord Dibben" wrote: Julie Be warned that xldown will stop at the first blank cell, which in a lot of cases will not be last used cell in a column. Best to use Roger's suggestion of going to bottom of sheet and working up to last row. Gord Dibben MS Excel MVP On Thu, 5 Oct 2006 07:29:02 -0700, julie cooke wrote: I found the answer on the programming bit of this site nad applied : Selection.End(xlDown).Select (was the bit we aready had) ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate Which gives the next row (or column) "Roger Govier" wrote: Hi Julie dim lastrow as long, nextcell as string LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row nextcell="A" & lastrow+1 -- Regards Roger Govier "julie cooke" <julie wrote in message ... We have a model with a macro to automate the process. The number of rows of data from both lots of input varies each time. We need to paste the second lot of data below the first lot. Does anyone know how to select the next cell boelow the last one on the first paste? When we record end down, down the macro uses an absolute cell reference and this stops the rest of the macro from working properly. We've tried find next blank and this goes to the next column. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Macro - next down
What I am getting at is to use xldown may not get you to last cell if you had a
blank cell further up in the column. If you "know" there will be no blanks above last entry, OK but Roger's method will never have to rely on that scenario. Also end down takes you to the row ABOVE the first blank cell, not the first blank cell. So that doesn't go to the correct place. But if you look at Roger's code you will see that he found the last entry in the column then back down 1 to the blank cell. dim lastrow as long, nextcell as string LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row nextcell="A" & lastrow+1 Gord On Fri, 6 Oct 2006 03:00:01 -0700, julie cooke wrote: The first response doesn't work and is too complex. We can already verify that the column in question always has data in all rows. If this is not always the case you can use end home and then home. This will take you to the bottom of the data in column A. You can then move right or left or up or down using my (now found) solution (for left and up use -). by the appropriate number of columns rows. Also end down takes you to the row ABOVE the first blank cell, not the first blank cell. So that doesn't go to the correct place. "Gord Dibben" wrote: Julie Be warned that xldown will stop at the first blank cell, which in a lot of cases will not be last used cell in a column. Best to use Roger's suggestion of going to bottom of sheet and working up to last row. Gord Dibben MS Excel MVP On Thu, 5 Oct 2006 07:29:02 -0700, julie cooke wrote: I found the answer on the programming bit of this site nad applied : Selection.End(xlDown).Select (was the bit we aready had) ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate Which gives the next row (or column) "Roger Govier" wrote: Hi Julie dim lastrow as long, nextcell as string LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row nextcell="A" & lastrow+1 -- Regards Roger Govier "julie cooke" <julie wrote in message ... We have a model with a macro to automate the process. The number of rows of data from both lots of input varies each time. We need to paste the second lot of data below the first lot. Does anyone know how to select the next cell boelow the last one on the first paste? When we record end down, down the macro uses an absolute cell reference and this stops the rest of the macro from working properly. We've tried find next blank and this goes to the next column. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel application.quit in macro problem | New Users to Excel | |||
How to stop Excel remembering/loading macro from previously opened Workbook | Excel Discussion (Misc queries) | |||
Macro Help In Excel | Excel Discussion (Misc queries) | |||
Closing File Error | Excel Discussion (Misc queries) | |||
passing arguments from an excel macro to a word macro | Excel Discussion (Misc queries) |