macro to fill column

mbl

New Member
Joined
Nov 1, 2004
Messages
10
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!
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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

New Member
Joined
Nov 1, 2004
Messages
10
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
Platform
  1. Windows
This code should allow for either option. Simply comment out the one you don't want to apply:
Code:
    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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,914
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top