MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to add a cell using autofill


Posted by Sid on July 07, 2001 2:10 PM

Column A contains a list AA100 AA101 AA102 etc. I want to put a macro on a button that will put the next value in the next blank cell of that column.
I am trying to combine Selection.End(xlDown).Select (to select the bottom cell) with Selection.AutoFill Destination:, but this requires a range which changes as rows are added. Can anyone help please?
Many thanx
Sid


Posted by Ivan F Moala on July 07, 2001 5:45 PM

Sid to get the last Row number use something like
this.....assumes NO blanks in your column data.

LastRowNumber = Cells(1, 1).End(xlUp).Row

so that your cell address to put the data in will
be Cells(LastRowNumber+1,1)


HTH
Ivan

Posted by Sid on July 09, 2001 4:12 PM

Posted by Sid on July 09, 2001 4:15 PM

Many thanx Ivan - got me thinking in the right direction

if its any use to anyone else heres what I figured

LastRowNumber = Range("A65536").End(xlUp).Row
Range("A" & LastRowNumber).Select
Selection.AutoFill Destination:=Range("A" & LastRowNumber, "A" & LastRowNumber + 1), Type:=xlFillDefault

Sid Sid to get the last Row number use something like