Concatenating two columns of data using a formula and the autofill command

[rahulkumar88]

Hi!
I need to concatenate two columns of data which are selected by the user. So the macro searches through the column headings to locate the required columns and then will concatenate them.
I'm able perform this task by running a loop through all the rows of data. The problem however is that I'm working with a significant amount of data (approx. 35000 rows) and it takes a couple of minutes for the loop to execute.
I was hoping to use a concatenation formula for the first row and then follow that up with the autofill command to work on the rest of the data. That process barely takes a few seconds when you do it manually. Unfortunately I'm unable to give the references for the required columns in the formula.
For example if I use the following formula
Range("J2").Formula = "=F2 & ""_"" & H2"
I want to know how to reference the user defined columns on the RHS.
I'd appreciate any help that you could provide.
Thanks in advance.

 

[mikerickson]

I wouldn't use formulas. I would build an array with the desired results and set the value of the result column at one fell swoop.

Dim firstColumn as Range
Dim secondColumn as Range
Dim resultColumn as Range, i As Long
Dim resultValues as Variant

Rem code to find firstColumn & secondColumn

Set resultColumn = Range("Z1")

resultValues = firstColumn.Values

For i = 1 to firstColumn.Rows.Count
    resultValues(i, 1) = CStr(resultValues(i, 1)) & "_" & CStr(secondColumn.Cells(i, 1))
Next i

resultColumn.Resize(firstColumn.Rows.Count, 1).Values = resultValues

 

[rahulkumar88]

Hi Mike,
Thanks for replying. I tried running your code but was unable to do so. I added the bit where the first column and second column are determined (I assumed the columns that were picked, so that I could test the rest of the code). But I get the following error "Object doesn't support this property or method" when the code gets to this line "
resultValues = firstColumn.Values". Could you please tell me what I'm doing wrong?

Sub test()
Dim firstColumn As Range
Dim secondColumn As Range
Dim resultColumn As Range, i As Long
Dim resultValues As Variant

lngLastRow = Cells(Rows.Count, "D").End(xlUp).Row
Range("D1:D" & lngLastRow).Select
Set firstColumn = Selection
Range("H1:H" & lngLastRow).Select
Set secondColumn = Selection
Set resultColumn = Range("Z1")

resultValues = firstColumn.Values

For i = 1 To firstColumn.Rows.Count
resultValues(i, 1) = CStr(resultValues(i, 1)) & "_" & CStr(secondColumn.Cells(i, 1))
Next i

resultColumn.Resize(firstColumn.Rows.Count, 1).Values = resultValues
End Sub

 

[mikerickson]

From the immediate window, what is firstColumn.Address when that error occurs?

 

[rahulkumar88]

If I run it in the immediate window I get a compile error("Invalid Use of Property"), but if I hover the cursor over firstColumn.address in the immediate window it shows "$D$1:$D$35715".

 

[mikerickson]

How much data do you have. Try this version.

Sub test()
Dim firstColumn As Range
Dim secondColumn As Range
Dim resultColumn As Range, i As Long
Dim resultValues As Variant

With Range("D:D")
    Set firstColumn = Range(.Cells(1,1), .Cells(.Rows.Count,1).End(xlup))
End With
Set secondColumn = Range("H1").Resize(firstColumn.Rows.Count, 1)

Set resultColumn = Range("Z1")

resultValues = firstColumn.Values

For i = 1 To firstColumn.Rows.Count
resultValues(i, 1) = CStr(resultValues(i, 1)) & "_" & CStr(secondColumn.Cells(i, 1))
Next i

resultColumn.Resize(firstColumn.Rows.Count, 1).Values = resultValues
End Sub

 

[rahulkumar88]

A lot of data. 35000 rows of it, which is why I'm trying to find the fastest way of carrying out the task.
I'm still getting the same error. "Object doesn't support this property or method" when the code gets to this line "resultValues = firstColumn.Values"

 

[mikerickson]

What is TypeName(firstColumn)?

Are you using Object Explicit?

 

[mikerickson]

I figured it out. The propery is Value , without an s

 

[rahulkumar88]

Nope, I'm not using Object Explicit. And firstColumn is a range object.