Results 1 to 8 of 8

Adding a count to concatenate range function

Threaded View

  1. #1
    Registered User
    Join Date
    02-11-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    6

    Adding a count to concatenate range function

    Hi all,

    I am looking to alter current coding in a concatenaterange function. See below:
    Function ConcatenateRange(ByVal cell_range As Range, _
                        Optional ByVal seperator As String) As String
    
    Dim cell As Range
    Dim newString As String
    Dim cellArray As Variant
    Dim i As Long, j As Long
    
    cellArray = cell_range.Value
    
    For i = 1 To UBound(cellArray, 1)
        For j = 1 To UBound(cellArray, 2)
            If Len(cellArray(i, j)) <> 0 Then
                newString = newString & (seperator & cellArray(i, j))
            End If
        Next
    Next
    
    If Len(newString) <> 0 Then
        newString = Right$(newString, (Len(newString) - Len(seperator)))
    End If
    
    ConcatenateRange = newString
    
    End Function
    Within the workbook I use CHAR(10) as a separator however I want the function to count the total cells to be concatenated in the range and attribute a separator such as 1) Cell Detail 1, 2) Cell Detail 2, etc. For example if I had the following data in each cell:
    A1: Thanks
    A2: for the
    A3: help!

    I want it to look like:

    B1: 1) Thanks
    2) for the
    3) help!

    Thanks in advance!
    - mbus
    Last edited by mbus; 02-12-2013 at 11:15 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1