Autofill from Activecell to Lastrow

[rhys_ack]

Hi All,

My first post to the forum. Usually I find I am able to solve any problems I have by browsing historic posts and amending solutions to suit my needs however this one has me stumped.

I have a worksheet with four columns (A,B,C & D) which is tens of thousands of rows long and grows each day. New information is added to columns A, B and C each morning and D is updated at the end of the day. I am trying to fill column D using a formula entered in to the first blank cell in column D and autofilling down until level with the last cell containing data in column A/B/C (all will be the same). This is part of a larger macro formatting a report and trying to move information across. I can select the start cell and enter the formula but cannot get the autofill part to work.

I have tried several solutions but the code below is what I have at the moment;

Dim LastRow As Long

LastRow = Range("C" & Rows.Count).End(xlUp).Row
Selection.AutoFill Destination:=(ActiveCell.Address & ":D" & LastRow)

Which gives the error "Run-time error '1004': AutoFill methodof range class failed."

As I said I've tried a number of things none of which worked and this is simply the most recent attempt. There may well be a completely different approach that works better.

Any help will be greatly appreciated,

Rhys

 

[ZAX]

Hi All,

My first post to the forum. Usually I find I am able to solve any problems I have by browsing historic posts and amending solutions to suit my needs however this one has me stumped.

I have a worksheet with four columns (A,B,C & D) which is tens of thousands of rows long and grows each day. New information is added to columns A, B and C each morning and D is updated at the end of the day. I am trying to fill column D using a formula entered in to the first blank cell in column D and autofilling down until level with the last cell containing data in column A/B/C (all will be the same). This is part of a larger macro formatting a report and trying to move information across. I can select the start cell and enter the formula but cannot get the autofill part to work.

I have tried several solutions but the code below is what I have at the moment;

Dim LastRow As Long

LastRow = Range("C" & Rows.Count).End(xlUp).Row
Selection.AutoFill Destination:=(ActiveCell.Address & ":D" & LastRow)

Which gives the error "Run-time error '1004': AutoFill methodof range class failed."

As I said I've tried a number of things none of which worked and this is simply the most recent attempt. There may well be a completely different approach that works better.

Any help will be greatly appreciated,

Rhys

After the data is updated,You can simply highlight the first cell with the formula (Let's say D2 is the first cell after the header) and after selecting D2,Double click on the small black square in the right bottom corner of the selected cell and the formula will be automatically copied to the last cell containing data in Column A or B or C.

 

[VoG]

Try

Dim LastRow As Long

LastRow = Range("C" & Rows.Count).End(xlUp).Row
ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & ":D" & LastRow)

 

[rhys_ack]

Thanks VoG, think I was just missing the 'Range' prefix for the destination. Doh!