Flexible Concatenation Function

[Rick Rothstein]

Rick, data can go into the adjacent columns to the right of the data fields. All is text, but that field shows an error because the cell starts with an "=".

I'm glad I asked about that #NAME? error as those lines of code required special handling. Give this macro a try...

Sub ConcatDashedRanges()
  Dim NextRow As Long, TempText As String, EmptyCells As Range, EqualSignCells As Range, Ar As Range
  On Error Resume Next
  Set EqualSignCells = Columns("A").SpecialCells(xlFormulas)
  For Each Ar In EqualSignCells
    Ar.Value = "'" & Ar.Formula
  Next
  Set EmptyCells = Columns("A").SpecialCells(xlConstants)
  For Each Ar In EmptyCells.Areas
    NextRow = NextRow + 1
    TempText = Application.Trim(Join(Application.Transpose( _
               Ar.Offset(1).Resize(Ar.Count - 1).Value), ";"))
    If Left(TempText, 1) = "=" Then TempText = "'" & TempText
    Cells(NextRow, "B").Value = TempText
  Next
End Sub

I highlighted in red the location where the delimiter is specified in case you want to change it in the future.

[javanchi]

I'm glad I asked about that #NAME? error as those lines of code required special handling. Give this macro a try...
I highlighted in red the location where the delimiter is specified in case you want to change it in the future.

Rick,

The macro worked very nicely. My team is very impressed!!

Can we nuance it to align the concatented line directly to the right of its starting point rather than at the top of the column?

JOHN

[Rick Rothstein]

Rick,

Can we nuance it to align the concatented line directly to the right of its starting point rather than at the top of the column?

Is this what you are looking for?

Sub ConcatDashedRanges()
  Dim TempText As String, EmptyCells As Range, EqualSignCells As Range, Ar As Range
  On Error Resume Next
  Set EqualSignCells = Columns("A").SpecialCells(xlFormulas)
  For Each Ar In EqualSignCells
    Ar.Value = "'" & Ar.Formula
  Next
  Set EmptyCells = Columns("A").SpecialCells(xlConstants)
  For Each Ar In EmptyCells.Areas
    TempText = Application.Trim(Join(Application.Transpose( _
               Ar.Offset(1).Resize(Ar.Count - 1).Value), ";"))
    If Left(TempText, 1) = "=" Then TempText = "'" & TempText
    Ar(1).Offset(1, 1).Value = TempText
  Next
End Sub

If you want the output aligned with the dashes instead of the first text line, change the red highlighted 1 (one) to a 0 (zero).