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

- - Latest reply:  Fruits and Veggies - Apr 7, 2010 at 10:39 PM
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
See more 

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
0
Thank you
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)
rizvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
> 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
rizvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
> 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
> rizvisa1
Posts
4476
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!