Macro Autofill help

[OFFLINEX]

I have been using the following formula multiple times with much success in one of my workbooks. But upon making a new one it gives me an error "Run-time error '1004': AutoFill method of Range class failed"

The only difference I can think of so far is I made the macros on excel 2007 and I'm trying to make these new ones on excel 2013... Buuuut my macros made in 07 work perfectly fine in my 13.

This is the code I am using.

Sub SPLI3()
'
' SPLI3 Macro
'


'
Dim lastrow As Long


lastrow = Range("D65000").End(xlUp).Row


Range("C1").Select
ActiveCell.FormulaR1C1 = "=TRIM(CLEAN(SUBSTITUTE(RC[-1],CHAR(160),"" "")))"
Selection.AutoFill Destination:=Range("C1:C" & lastrow)
Range("C1:C5000").Select
End Sub

The debugger Highlights the "Selection.AutoFill Destination:=Range("C1:C" & lastrow)" line.

Any help would be appreciated.
Thanks

 

[JoeMo]

Try changing this line:

Selection.AutoFill Destination:=Range("C1:C" & lastrow)

to this:

Range("C1:C" & lastrow).FillDown

 

[OFFLINEX]

It still gives an error.

Run-time error '1004':

FillDown method of Range class failed

It highlights the line "Range("C1:C" & lastrow).FillDown"

 

[Ken Cowen]

That is the error I get if column D is blank. Do you have anything in column D?
Not sure why the autofill breaks if the range is a single row but it seems to.

Ken

 

[OFFLINEX]

I tried moving data into column D still gives me the original error. Its the weirdest thing I have another macro that uses this EXACT line of code made on a different computer and it runs like a charm.

 

[SQUIDD]

i have tested your code, works for me.

I also changed it to this, and this works to.

Sub test()
Dim lastrow As Long
lastrow = Range("D65000").End(xlUp).Row
With Range("C1")
.FormulaR1C1 = "=TRIM(CLEAN(SUBSTITUTE(RC[-1],CHAR(160),"" "")))"
.AutoFill Destination:=Range("C1:C" & lastrow)
End With
End Sub

dave

 

[SQUIDD]

one more thing, are you sure you are on the correct sheet before you run the macro.

dave

 

[Michael M]

what about

Sub test()
Dim lr As Long
lr = Cells(Rows.Count, "D").End(xlUp).Row
Range("C1:C" & lr).Formula = "=TRIM(CLEAN(SUBSTITUTE(B1,CHAR(160),"" "")))"
End Sub

 

[SQUIDD]

do you run code before this.

try putting in

sheets("your sheet with data").activate

at the top

dave

 

[OFFLINEX]

i have tested your code, works for me. I also changed it to this, and this works to.

Sub test() Dim lastrow As Long lastrow = Range("D65000").End(xlUp).Row With Range("C1") .FormulaR1C1 = "=TRIM(CLEAN(SUBSTITUTE(RC[-1],CHAR(160),"" "")))" .AutoFill Destination:=Range("C1:C" & lastrow) End With End Sub

dave

I still get an error with the line .AutoFill Destination:=Range("C1:C" & lastrow) I am on the right sheet.