macro to fill column

[mbl]

I'm trying to use a macro to copy the value in row 2 of a column and paste it in all the cells below. I would like to use it in columns of variable lengths. I had a macro that was working (although it always gave me a few extra rows of the value), but now it doesn't. I didn't think I'd made any changes to it, but here's what I have now:

Sub fill_column()
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1:A" & Range("A1000").End(xlUp).Row).Select
ActiveSheet.Paste
End Sub

What do I have wrong?

Thanks in advance!

 

[Joe4]

You need to subtract the row number that you are in from your formula if you want it to end at the right place. Also be aware that you are using column A to determine your last row.

Sub fill_column()
   Selection.Copy
   ActiveCell.Offset(1, 0).Range("A1:A" & Range("A1000").End(xlUp).Row - ActiveCell.Row).Select
   ActiveSheet.Paste
End Sub

 

[mbl]

I was not aware that the A reference was going back to column A -- that explains the problem because I recently added a column and have not entered the data there yet.

I tried referencing B in the code instead, but then it pasted the values one column to the right. How can I fix that? And while I'm asking, how can I get it to stop giving me the two extra rows?

 

[Joe4]

how can I get it to stop giving me the two extra rows?

I already answered that.

You need to subtract the row number that you are in from your formula if you want it to end at the right place

That is the "- ActiveCell.Row" part I added to your formula.

I tried referencing B in the code instead, but then it pasted the values one column to the right. How can I fix that?

OK, before we can devise a good solution, we need to develop some rules:
Are you always going to be referencing column B to determine the column length to copy down to, or is it a more relative rule (i.e. always look at the previous column, etc.)

 

[Joe4]

This code should allow for either option. Simply comment out the one you don't want to apply:

    Dim myLastRow As Long
    
'   If determine last row for column B
''    myLastRow = Range("B65536").End(xlUp).Row
    
'   If determine last row from previous column
    myLastRow = Cells(65536, ActiveCell.Column - 1).End(xlUp).Row
    
    ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(myLastRow, ActiveCell.Column)), Type:=xlFillDefault