Macro that can merge a column of cells into one cell separated by semi colon

**glide2131** · 06-25-2013, 04:39 PM

Hello,

I need a macro that can take all the values of all the cells in a single column (I) and combine them into one cell (J11) separated by a semicolon.
I also need to macro to work across all worksheets except for "Sheet1", "Sheet2" and "Sheet3".

I found the following code which works well but it does not seperate by semi colon or work across all sheets.

 
Dim v As Variant
Dim s As String
Dim i As Long

' Fetch from sheet
v = Range("A1:A100").Value

' Concatenate into a single string
s = ""
For i = LBound(v, 1) To UBound(v, 1)
    s = s & v(i, 1)
Next i

' Put back on sheet
Range("B1").Value = s

Can anyone help me with this?
Thanks

**Debraj Roy** · 06-25-2013, 04:47 PM

Hi Glide!

Try This..

Dim v As Variant
Dim s As String
Dim i As Long
For Each sh In ActiveWorkbook
 Select Case sh.Name
   Case "Sheet1", "Sheet2" And "Sheet3"
   Case Else
     With sh
     ' Fetch from sheet
     v = .Range("A1:A100").Value
     
     ' Concatenate into a single string
     s = ""
     For i = LBound(v, 1) To UBound(v, 1)
         s = s & ";" & v(i, 1)
     Next i
     
     ' Put back on sheet
     .Range("B1").Value = Mid(s, 2)
    End With
 End Select

**AB33** · 06-25-2013, 05:01 PM

v = .Range("A1:A100").Value

Column I

**Debraj Roy** · 06-25-2013, 05:09 PM

@ AB33!

Thanks for the catch..

Dim v As Variant
Dim s As String
Dim i As Long
For Each sh In ActiveWorkbook
 Select Case sh.Name
   Case "Sheet1", "Sheet2" And "Sheet3"
   Case Else
     With sh
     ' Fetch from sheet
     v = .Range("I1:I" & Cells(Rows.Count, "I").End(-4162)).Value
     
     ' Concatenate into a single string
     s = ""
     For i = LBound(v, 1) To UBound(v, 1)
         s = s & ";" & v(i, 1)
     Next i
     
     ' Put back on sheet
     .Range("B1").Value = Mid(s, 2)
    End With
 End Select

**glide2131** · 06-25-2013, 05:12 PM

Thank you that works great! However, I did notice that it places a semi colon for each row regardless of whether there was a value in that cell. Do you know how to modify this code so that it only copies values if there is a value in the cell? I am trying to avoid a long list of semicolons. Thanks a ton.

**Debraj Roy** · 06-25-2013, 05:23 PM

Hi Glide

Try This..

Dim v As Variant
Dim s As String
Dim i As Long
For Each sh In ActiveWorkbook.Worksheets
 Select Case sh.Name
   Case "Sheet1", "Sheet2", "Sheet3"
   Case Else
     With sh
     ' Fetch from sheet
     v = .Range("I1:I" & .Cells(.Rows.Count, "I").End(-4162).Row)
     
     ' Concatenate into a single string
     s = ""
     For Each cl In .Range("I1:I" & .Cells(.Rows.Count, "I").End(-4162).Row).SpecialCells(2)
        If Not cl Is Nothing Then _
         s = s & ";" & cl
     Next cl
     
     ' Put back on sheet
     .Range("B1").Value = Mid(s, 2)
    End With
 End Select
Next sh

Macro that can merge a column of cells into one cell separated by semi colon

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro that can merge a column of cells into one cell separated by semi colon

Re: Macro that can merge a column of cells into one cell separated by semi colon

Re: Macro that can merge a column of cells into one cell separated by semi colon

Re: Macro that can merge a column of cells into one cell separated by semi colon

Re: Macro that can merge a column of cells into one cell separated by semi colon

Re: Macro that can merge a column of cells into one cell separated by semi colon

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions