Concatenate multiple ranges using vba

by

Here is my ConcatenateRange. It allows you to add a seperator if you please. It is optimized to handle large ranges since it works by dumping the data in a variant array and working with it within VBA.

You would use it like this:

=ConcatenateRange(A1:A10)

The code:

Function ConcatenateRange(ByVal cell_range As range, _
                    Optional ByVal seperator As String) As String

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

More Related Contents:

  1. VBA comma separated cells, needt last entry only
  2. How can I find last row that contains data in a specific column?
  3. Open Excel file for reading with VBA without display
  4. Wait for shell command to complete [duplicate]
  5. How to return the number of dimensions of a (Variant) variable passed to it in VBA [duplicate]
  6. How to add a named sheet at the end of all Excel sheets?
  7. Find last used cell in Excel VBA
  8. VBA general way for pulling data out of SAP [closed]
  9. Does Dir() make any guarantee on the order of files returned?
  10. Call a Sub with a String
  11. Delete Row based on Search Key VBA
  12. How to add a button programmatically in VBA next to some sheet cell data?
  13. How to add parameters to an external data query in Excel which can’t be displayed graphically?
  14. ADODB Connection String for .csv
  15. Pivotfields multiple filter
  16. How to send mail when the .Send does not work?
  17. How to clear memory to prevent “out of memory error” in VBA?
  18. Extract Data from PDF and Add to Worksheet
  19. EXCEL VBA Check if entry is empty or not ‘space’
  20. Paste Excel Chart into Powerpoint using VBA
  21. running excel macro from another workbook
  22. Multiply Entire Range By Value?
  23. EXCEL VBA UserInterfaceOnly:= True not working
  24. VBA Excel – IFERROR & VLOOKUP error
  25. Finding a workbook in one of multiple Excel instances
  26. SAP Extract into EXCEL via VBA out of memory
  27. reading entire text file using vba
  28. Convert month name into number
  29. How to protect Excel workbook using VBA?
  30. How to get the range of the visible rows after applying an advanced filter in Excel (VBA)

Leave a Comment