Macro that goes to next blank cell [Solved/Closed]

Report
-
 Fruits and Veggies -
Hello,
im using excel 2007 and I have a cell which I input a number inn and then a macro that runs copying the contents to another column which then adds the contents up. but when the macro is run again I want it to copy the contents into the next blank cell within that column every time it is re run.

here's the macro

1 reply

Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
757
I dont know how you are getting to that column/cell

but this gives you last row that has been used


lastrow=cells(65536, 1).end(xlup).row

1 refers to column A, it finds the last used column in A (unless nothing is in col A in which case 1 is returned)
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
757 > dbibb
I dont see it. It says there is the macro, where is the code ?
oh yea ops I guess the copy paste didnt work... sorry here

Sub prt()
'
' prt Macro
'

'
Range("B10").Select
Selection.Copy
Range("J2").Select
ActiveSheet.Paste
Range("B10").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("B11").Select

End Sub
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
757 > dbibb
Of course. You are explicitly telling it to paste at cell J2 (just as you are doing for B10)

replace

Range("J2").Select


by

Cells(ActiveSheet.Rows.Count, "J").End(xlUp).Offset(1, 0).Select

and consider if you have to do for B10 also. It would be same way as was done for J
>
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016

wow thanks a lot it worked just like I wanted

hey thanks for the help.....I used the piece of code in a project I was working on! Thanks for sharing!