Excel

Concatenate all Data in Range (Function)

Ease of Use

Intermediate

Version tested with

2000, 2003 

Submitted by:

johnske

Description:

After running the code all data in the chosen range is concatenated. 

Discussion:

The Excel concatenate worksheet function requires each cell and delimiter be entered, this function is much more compact and can either be used as a VBA or worksheet function (UDF)... Data concatenation can be desired for many reasons, one reason may be to check rows or columns for duplicated rows or columns. 

Code:

instructions for use 

			
Option Explicit 
 
Sub CatenateIt() 
     'replace A1:G2 below with your own range
     'DEMO: concatenate A1 to G2
    MsgBox Catenate(Range("A1:G2"), " ") 
End Sub 
 
 
 
 '<< Function to concatenate cells in a range >>
Public Function Catenate(MyRange As Range, _ 
    Optional Delimiter As String) As String 
    Dim Cell As Range, N As Long 
    N = 1 
     'go thru MyRange cell by cell and concatenate
    For Each Cell In MyRange 
        If N = MyRange.Cells.Count Then 
             'we don't need a delimiter after last cell
            Catenate = Catenate & Cell 
        Else 
             'otherwise we do need a delimiter
            Catenate = Catenate & Cell & Delimiter 
        End If 
        N = N + 1 
    Next Cell 
    Set Cell = Nothing 
End Function

How to use:

  1. Open an Excel workbook
  2. Select Tools/Macro/Visual Basic Editor
  3. In the VBE window, select Insert/Module
  4. Copy and paste the code into the Module
  5. Select File/Close and Return To Microsoft Excel
  6. Save your work
 

Test the code:

  1. Download the attachment, extract the workbook, view and test the examples in it...
 

Sample File:

Concatenate.zip 10.51KB 

Approved by mdmackillop


This entry has been viewed 243 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express