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.

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.

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.

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.

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.

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.

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.