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

dbibb - Mar 3, 2010 at 03:35 PM - 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 

9 replies

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Mar 3, 2010 at 04:48 PM
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 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen > dbibb - Mar 4, 2010 at 03:42 PM
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 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen > dbibb - Mar 4, 2010 at 04:08 PM
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
dbibb > rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Mar 5, 2010 at 12:24 AM
wow thanks a lot it worked just like I wanted
Fruits and Veggies - Apr 7, 2010 at 10:39 PM
hey thanks for the help.....I used the piece of code in a project I was working on! Thanks for sharing!