Results 1 to 5 of 5

Concatenate Columns using VBA

This is a discussion on Concatenate Columns using VBA within the Excel Questions forums, part of the Question Forums category; Hi Guys, I need a code that will perform below requirements. Excel VBA Concatenate Columns from A1 to AZ1 and ...

  1. #1
    New Member
    Join Date
    Aug 2011
    Posts
    3

    Smile Concatenate Columns using VBA

    Hi Guys,

    I need a code that will perform below requirements.

    Excel VBA Concatenate Columns from A1 to AZ1 and so on.. then it will loop untill it reaches to last row e.g A10000 to B10000 and so on.. refer to below:
    A1 = ABC
    B1 = DEF
    C1 = GHI
    A2 = 123
    B2 = 456
    C2 = 789
    result in the other sheets.
    A1 = ABCDEFGHI
    A2 = 123456789

    Any help will be highly appreciated

    Brgds,
    Japhet

  2. #2
    Board Regular dave3009's Avatar
    Join Date
    Jun 2006
    Location
    Glasgow, Scotland
    Posts
    6,292

    Default Re: Concatenate Columns using VBA

    Hi and welcome

    try this

    Code:
    Sub CatS1toS2()
    Dim lstRw As Long
    Dim lstCl As Long
    Dim i As Long
    Dim j As Long
    Dim result As Variant
    lstRw = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 1 To lstRw
        lstCl = Sheet1.Cells(i, Columns.Count).End(xlToLeft).Column
            For j = 1 To lstCl
                result = result & Sheet1.Cells(i, j)
            Next j
            Sheet2.Range("A" & i) = result
            result = ""
    Next i
    
    End Sub
    Please state your version of Excel, I use Excel 2007 on Win 7.
    Back up all data before testing VBA codes, and please use [code] tags.
    davehouston.co.uk/

  3. #3
    Board Regular
    Join Date
    Oct 2010
    Posts
    198

    Default Re: Concatenate Columns using VBA

    Try:

    Code:
    Option Explicit
    Sub CombineColumnstoColumn()
    Dim icell As Long, lastrow As Long, lastcol As Long, iConc As Long
    Dim myValue As String
     
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
     
    Application.ScreenUpdating = False
    For icell = 1 To lastrow
        lastcol = Cells(icell, Columns.Count).End(xlToLeft).Column
        myValue = Cells(icell, 1).Value
            For iConc = 2 To lastcol
                myValue = myValue & Cells(icell, iConc).Value
            Next iConc
        Range("A" & icell).Value = myValue
    Next icell
    Application.ScreenUpdating = True
    End Sub

  4. #4
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    16,617

    Default Re: Concatenate Columns using VBA

    Put =ConcatIf(Sheet1!$1:$1, "<>", Sheet1!$1:$1, "") in Sheet2!a1

    Code:
    Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
                        Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
                        
        Rem the first three argumens of ConcatIf mirror those of SUMIF
        Rem the Delimiter and NoDuplicates arguments are optional (default "" and False)
        Dim i As Long, j As Long
        
        With compareRange.Parent
            Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
        End With
        
        If compareRange Is Nothing Then Exit Function
        If stringsRange Is Nothing Then Set stringsRange = compareRange
        Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                                stringsRange.Column - compareRange.Column)
        
        For i = 1 To compareRange.Rows.Count
            For j = 1 To compareRange.Columns.Count
                If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                    If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                        ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                    End If
                End If
            Next j
        Next i
        ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
    End Function

  5. #5
    New Member
    Join Date
    Aug 2011
    Posts
    3

    Default Re: Concatenate Columns using VBA

    Wow! thanks a lot guyz.. I think I will give the credits for dave now since he was the first one gives the solution and I'm currently using it right now. stnkynts and mikerikson and dave, you guyz are gem!

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com