Excel Macro - next down

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Hi Julie

dim lastrow as long, nextcell as string
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

nextcell="A" & lastrow+1
 
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)
 
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
 
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.
 
Hi Julie
The first response doesn't work and is too complex.
Click to expand...
Obviously it is your prerogative as to whether you accept any advice
offered freely and in all good faith.
too complex
Click to expand...
Again, that is a matter of your opinion
doesn't work
Click to expand...
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
Click to expand...

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.
 
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.
Click to expand...

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
 
Back
Top