Home > Back-end >  Efficient way to concatenate two cells
Efficient way to concatenate two cells

Time:03-11

Currently, the below code combined the "top" and "bottom" cell and sets the value of the "bottom" cell as the combined value. Iterating through thousands of columns, it's slower than I thought.

Dim wb As Workbook
Dim ws As Worksheet
Dim topCell As String, bottomCell As String, combinedCell As String

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")

For i = 1 To 10000
    topCell = ws.Cells(1,i).Value
    bottomCell = ws.Cells(2,i).Value
    bottomCell = Format(bottomCell, "yyyy")
    combinedCell = topCell & " " & bottomCell
Next i

ws.Rows(1).EntireRow.ClearContents

Is there a more efficient way to go about this?

CodePudding user response:

To speed up VBA, you need to reduces the access from VBA to Excel because that is what makes your code slow. Read all data at once into a (2-dimensional) array, loop over that array and then write back the array.

Dim r As Range, data As Variant, col As Long
' Set Range and read values into Array
With ThisWorkbook.Sheets(1)
    Set r = .Range(.Cells(1, 1), .Cells(2, 10000))
End With
data = r.Value
' Process data
For col = LBound(data, 2) To UBound(data, 2)
    data(2, col) = data(1, col) & Format(data(2, col), "yyyy")
    data(1, col) = ""
Next
' Write back
r.Value = data

CodePudding user response:

Concat Rows in 10k Columns (Evaluate)

Option Explicit

Sub ConcatRows()
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    
    With ws.Range("A1:NTP2")
        .Rows(2).Value = ws.Evaluate(.Rows(1).Address _
            & "&"" ""&" & "TEXT(" & .Rows(2).Address & ",""yyyy"")")
        .Rows(1).ClearContents
    End With

End Sub

Barely Related (Bonus?)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns the Excel column string from a (column) number.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function GetColumnString( _
    ByVal ColumnNumber As Long) _
As String
    Dim Remainder As Long
    Do
        Remainder = (ColumnNumber - 1) Mod 26
        GetColumnString = Chr(Remainder   65) & GetColumnString
        ColumnNumber = Int((ColumnNumber - Remainder) \ 26)
    Loop Until ColumnNumber = 0
End Function


Sub GetColumnStringTESTandHelp()
    Debug.Print GetColumnString(10000) ' result 'NTP'
    Range("NTP1").Select
    Range("A1:NTP1").EntireColumn.AutoFit
End Sub
  • Related