So I have this Macro
Code
Sub combineRows()
Dim rg As Range
Dim delimiter As String, s As String
Dim i As Long, k As Long, n As Long, nData As Long
Dim vData As Variant, vResults As Variant
delimiter = ", "
Set rg = Range("A2").CurrentRegion
Set rg = rg.Offset(1, 0).Resize(rg.Rows.Count - 1, rg.Columns.Count)
n = rg.Rows.Count
nData = Application.CountA(rg.Columns(1))
vData = rg.Value
ReDim vResults(1 To nData, 1 To 2)
For i = 1 To n
If vData(i, 1) <> "" Then
k = k + 1
vResults(k, 1) = vData(i, 1)
If s <> "" Then
If i > 1 Then vResults(k - 1, 2) = s
End If
s = IIf(vData(i, 2) = "", "", vData(i, 2))
Else
If vData(i, 2) <> "" Then
If s = "" Then
s = vData(i, 2)
Else
s = s & delimiter & vData(i, 2)
End If
End If
End If
Next
If s <> "" Then vResults(k, 2) = s
rg.ClearContents
rg.Resize(nData, 2).Value = vResults
End Sub
the in theory should run until the end of the file ( last row of column A ) and concatenate the values from B column to just 1 row.
The problem is that the macro stops without any error..
Any VBA experts here that can chip in?
also, the excel file itself have more then 140K rows so maybe that the problem I don't know