Using VBA for autofill

[Burrgogi]

I've got some text in col. A, B, C.

So for example:
A1 = kayak
B1 = tools
C1 = Zebra

In D1, I've been manually typing in the following:
=CONCATENATE(A1,B1,C1)
And then I'll do an autofill down to the very last cell which might be D100 or D112... It depends on how long my list is.

Basically I recorded a macro but the problem is that since the length of my list varies, how do I tell VBA to continue autofill on col. D as long as there some value contained in col. A?

Sub test()
    With Range("D:D")
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-2],RC[-1])"
        .Value = .AutoFill
    End With
End Sub

When I run this code, VBA gives me an error message: "Argument Not optional".

I'm using Office 2007, BTW.

 

[Tom Urtis]

Sub Test2()
Range("D1:D" & Cells(Rows.Count, 1).End(xlUp).Row).FormulaR1C1 = "=RC1&RC2&RC3"
End Sub

 

[Burrgogi]

Sub Test2()
Range("D1:D" & Cells(Rows.Count, 1).End(xlUp).Row).FormulaR1C1 = "=RC1&RC2&RC3"
End Sub

Tom - thank you for your quick response. That worked. Can you tell me what I did wrong with my VBA code above?

 

[Tom Urtis]

You were looking to populate blank cells in all of column D which might be over 1 million, instead of identifying the last row where something is in a cell in column A.

Also, I see you are only wanting to end up with values, so my code should have been

Sub Test2()
With Range("D1:D" & Cells(Rows.Count, 1).End(xlUp).Row)
.FormulaR1C1 = "=RC1&RC2&RC3"
.Value = .Value
End With
End Sub


Your code was trying to turn AutoFill, a method, into a value, a property.

 

[Marcelo Branco]

Hi,

Maybe this (try it on a test-workbook)

Sub test()
    With Range("D1:D" & Cells(Rows.Count, "A").End(xlUp).Row)
        .FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-2],RC[-1])"
    End With
End Sub

-Edit-

I hadnt seen the previous posts


HTH

M.

 

[Burrgogi]

You were looking to populate blank cells in all of column D which might be over 1 million, instead of identifying the last row where something is in a cell in column A.

Also, I see you are only wanting to end up with values, so my code should have been

Sub Test2()
With Range("D1:D" & Cells(Rows.Count, 1).End(xlUp).Row)
.FormulaR1C1 = "=RC1&RC2&RC3"
.Value = .Value
End With
End Sub


Your code was trying to turn AutoFill, a method, into a value, a property.

Excellent - thank for that helpful explanation. I really gotta get my hands on a excel VBA primer. This stuff has fascinated me for years - it's about time I start to learn it on my own.