• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Concatenation- missing Range or Array code

AAP

Member
Hi Everyone, I want to concatenate two different columns into a new column. Column 44 and Column 35 should be merged into column 45. My sheet contains 1000000 rows, the For Loop works code works fine as given below. But it is not efficient to do the task quickly. I am missing range or array to include in concatenation so that i work quickly. Any advice please :)

[Cells(2, 45).Select
For i = 2 To LastRow
ActiveCell.Value = Cells(i, 44).Value & Cells(i, 35).Value
ActiveCell.Offset(1, 0).Select
Next i]
 
Code:
lastrow=......
arr=cells(2,44).resize(lastrow-1,1)
brr=cells(2,35).resize(lastrow-1,1)
redim crr(1 to lastrow-1,1)
for i=1 to ubound(arr)
      crr(i,1)=arr(i,1) & brr(i,1)
next
cells(2,45).resize(ubound(crr),1)=crr
take a try ??
 
Its not working in the file attached. Please see thanks a lot for help.
 

Attachments

  • Rydding 2015 Test.xlsm
    172.3 KB · Views: 0
Hi ,

I am not sure how fast this will be :
Code:
Sub check()
    Dim LastRow As Long, LastColumn As Long, size As Integer, size2 As Integer, i As Integer

    ThisWorkbook.Worksheets("DB").Activate
    If WorksheetFunction.CountA(Cells) > 0 Then
      LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
      LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    End If

    Cells(2, 45).Resize(LastRow - 1, 1).FormulaArray = Application.Evaluate("=" & Cells(2, 44).Resize(LastRow - 1, 1).Address(ReferenceStyle:=xlR1C1) & " & " & Cells(2, 35).Resize(LastRow - 1, 1).Address(ReferenceStyle:=xlR1C1))
End Sub

Basically a concatenate is a slow operation , and if you are talking of a million rows , I don't think any method will be fast.

Narayan
 
Hi ,

I am not sure how fast this will be :
Code:
Sub check()
    Dim LastRow As Long, LastColumn As Long, size As Integer, size2 As Integer, i As Integer

    ThisWorkbook.Worksheets("DB").Activate
    If WorksheetFunction.CountA(Cells) > 0 Then
      LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
      LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    End If

    Cells(2, 45).Resize(LastRow - 1, 1).FormulaArray = Application.Evaluate("=" & Cells(2, 44).Resize(LastRow - 1, 1).Address(ReferenceStyle:=xlR1C1) & " & " & Cells(2, 35).Resize(LastRow - 1, 1).Address(ReferenceStyle:=xlR1C1))
End Sub

Basically a concatenate is a slow operation , and if you are talking of a million rows , I don't think any method will be fast.

Narayan

Many Many thanks, Narayan for helping me and shed light on concatenation.
 
Hi ,

Use this line instead of the existing :

Cells(2, 45).Resize(LastRow - 1, 1).FormulaArray = Application.Evaluate("=" & Cells(2, 44).Resize(LastRow - 1, 1).Address(ReferenceStyle:=xlR1C1) & " & " & """" & " " & """" & " & " & Cells(2, 35).Resize(LastRow - 1, 1).Address(ReferenceStyle:=xlR1C1))

The highlighted portion is to be added.

Narayan
 
Hi ,

Use this line instead of the existing :

Cells(2, 45).Resize(LastRow - 1, 1).FormulaArray = Application.Evaluate("=" & Cells(2, 44).Resize(LastRow - 1, 1).Address(ReferenceStyle:=xlR1C1) & " & " & """" & " " & """" & " & " & Cells(2, 35).Resize(LastRow - 1, 1).Address(ReferenceStyle:=xlR1C1))

The highlighted portion is to be added.

Narayan

Hi Narayan, don't know it took 10 minutes to complete 200,000 rows. But then i tried worksheet formula and it took 5 seconds to complete same number of rows. I didn't understand the reason of difference. I tried to concatenate column 1 and column 4 in column 5.
[
[/Range("E2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-4],"" "",RC[-1])"
Selection.AutoFill Destination:=Range(Cells(2, 5), Cells(LastRow, 5))

Range(Cells(2, 5), Cells(LastRow, 5)).Copy
Range("E2").Select
Selection.PasteSpecial xlPasteValues]]
 
Hi ,

Sorry , but I am as much in the dark as you are ; there are different ways of doing the same thing , and unlike in the case of OFFSET and INDEX , these cases have not been as much researched as you would like them to be.

By your trial and error you can see which technique is the most efficient.

Narayan
 
Hi, it's true. But, I am really glad with your prompt help and advice and really by heart thankful to you.
 
Back
Top