Concatenate every 100 rows into one cell with VBA

satish78

Board Regular
Joined
Aug 31, 2014
Messages
207
Hi Friends,

Trying to concatenate every 100 rows into one cell
=Concatenate(C1:C100)
=Concatenate(C101:C200)
=Concatenate(C201:C300) and so on.
I have 50K rows to concatenate/combine cells

Above formula is not working. I know that VBA macro can do this task. Every time I have to specific number of rows to combine. For example, sometimes every 9 rows or every 500 or 1000 rows to combine.
It would be great if a macro ask me to specify every number of rows to combine.

Hope you understand.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,558
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Sub ConcatData()

    Dim Ans As String
    Dim Cnt As Long
    Dim Cl As Range
    Dim Rw As Long
    
    Ans = InputBox("How many rows do you want to use?")
    If Len(Ans) = 0 Then Exit Sub
    Rw = 1
    For Cnt = 1 To Range("C" & Rows.Count).End(xlUp).row Step Ans
        For Each Cl In Range("C" & Cnt).Resize(Ans)
            If Range("A" & Rw).Value = "" Then
                Range("A" & Rw).Value = Cl
            Else
                Range("A" & Rw).Value = Range("A" & Rw).Value & "," & Cl.Value
            End If
        Next Cl
        Rw = Rw + 1
    Next Cnt

End Sub
This will put the concatenated strings in A1 downwards
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,973
Office Version
  1. 2016
Platform
  1. Windows
How about
Code:
Sub ConcatData()

    Dim Ans As String
    Dim Cnt As Long
    Dim Cl As Range
    Dim Rw As Long
    
    Ans = InputBox("How many rows do you want to use?")
    If Len(Ans) = 0 Then Exit Sub
    Rw = 1
    For Cnt = 1 To Range("C" & Rows.Count).End(xlUp).row Step Ans
        For Each Cl In Range("C" & Cnt).Resize(Ans)
            If Range("A" & Rw).Value = "" Then
                Range("A" & Rw).Value = Cl
            Else
                Range("A" & Rw).Value = Range("A" & Rw).Value & "," & Cl.Value
            End If
        Next Cl
        Rw = Rw + 1
    Next Cnt

End Sub
This will put the concatenated strings in A1 downwards
The following somewhat more compact macro will produce the same output as your code does...
Code:
[table="width: 500"]
[tr]
	[td]Sub ConcatData()

    Dim R As Long, Ans As String, Txt As String
    
    Ans = InputBox("How many rows do you want to use?")
    For R = 1 To Cells(Rows.Count, "C").End(xlUp).Row Step Ans
      Range("A1").Offset(Int((R - 1) / Ans)) = Join(Application.Transpose(Cells(R, "C").Resize(Ans)), ",")
    Next
    
End Sub[/td]
[/tr]
[/table]
However, I note that if the list in Column C is not an exact multiple of the number inputted by the user, both of our codes will put a trail of consecutive commas at the end of the text in the last data cell outputted to Column A. The following revision to my macro will eliminate those trailing commas, but for it to work correctly, none of the cells internal to the values in Column C can be blank.
Code:
[table="width: 500"]
[tr]
	[td]Sub ConcatData()

    Dim R As Long, Ans As String, Txt As String
    
    Ans = InputBox("How many rows do you want to use?")
    For R = 1 To Cells(Rows.Count, "C").End(xlUp).Row Step Ans
      Txt = Join(Application.Transpose(Cells(R, "C").Resize(Ans)), ",")
      Range("A1").Offset(Int((R - 1) / Ans)) = Left(Txt, InStr(Txt & ",,", ",,") - 1)
    Next
    
End Sub[/td]
[/tr]
[/table]
 

Fennek

Active Member
Joined
Nov 21, 2016
Messages
279
Hello,

a bit late:

Code:
sub Untested()
for i = 1 to cells(rows.count, 3).end(xlup).row step 100
r=r+1
cells(r,4) = join(application.transpose(range("C" & i & ":C" & i+99)), ", ")
next i
end sub

regards
 

satish78

Board Regular
Joined
Aug 31, 2014
Messages
207
Hi Rick,

Your last macro did the magic what I want and also popup box where I have an opportunity to specify the number whatever I need.
"," is an added advantage.

Thanks Rick you saved my time.
Sub ConcatData()

Dim R As Long, Ans As String, Txt As String

Ans = InputBox("How many rows do you want to use?")
For R = 1 To Cells(Rows.Count, "C").End(xlUp).Row Step Ans
Txt = Join(Application.Transpose(Cells(R, "C").Resize(Ans)), ",")
Range("A1").Offset(Int((R - 1) / Ans)) = Left(Txt, InStr(Txt & ",,", ",,") - 1)
Next

End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,557
Messages
5,637,043
Members
416,955
Latest member
Gohar hussain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top