Combining text from multiple cells into one cell with carriage return

[PrimeTimeAction]

I want to make macro to combine a group of data (separated by blank rows) from multiple cells in a column into one cell with carriage return (alt+enter) between each cell value. Can somebody help me with this.
see below:

[TABLE="width: 281"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 281"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[silentwolf]

Hi,

vbCrlf ist what that does.. Have a look at the online help..

 

[Rick Rothstein]

I want to make macro to combine a group of data (separated by blank rows) from multiple cells in a column into one cell with carriage return (alt+enter) between each cell value

Is it ever possible for there to be more topics than than lines of text belonging to those topics?

 

[Rick Rothstein]

Is it ever possible for there to be more topics than than lines of text belonging to those topics?

You can ignore the above question I asked in Message #3 , I have found a solution for which it does not matter. The only thing this code requires that there be at least on line of text at minimum for each set of grouped topics.

[table="width: 500"]
[tr]
	[td]Sub ConcatTopicsLinesOfText()
  Dim X As Long, Ar As Range, RngB As Range, RngC As Range, Result As Variant
  Set RngB = Columns("B").SpecialCells(xlConstants)
  Set RngC = Columns("C").SpecialCells(xlConstants)
  ReDim Result(1 To Application.Max(RngB.Areas.Count, RngC.Areas.Count), 1 To 2)
  For Each Ar In RngB.Areas
    X = X + 1
    If Ar.Rows.Count = 1 Then
      Result(X, 1) = Ar
    Else
      Result(X, 1) = Join(Application.Transpose(Ar.Columns(1)), vbLf)
    End If
  Next
  X = 0
  For Each Ar In RngC.Areas
    X = X + 1
    If Ar.Rows.Count = 1 Then
      Result(X, 2) = Ar
    Else
      Result(X, 2) = Join(Application.Transpose(Ar.Columns(1)), vbLf)
    End If
  Next
  Range("B20").Resize(UBound(Result), 2) = Result
End Sub[/td]
[/tr]
[/table]

 

[sandy666]

for information, alternatively to VBA, you can use PowerQuery

 

[PrimeTimeAction]

You can ignore the above question I asked in Message #3 , I have found a solution for which it does not matter. The only thing this code requires that there be at least on line of text at minimum for each set of grouped topics.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub ConcatTopicsLinesOfText()
  Dim X As Long, Ar As Range, RngB As Range, RngC As Range, Result As Variant
  Set RngB = Columns("B").SpecialCells(xlConstants)
  Set RngC = Columns("C").SpecialCells(xlConstants)
  ReDim Result(1 To Application.Max(RngB.Areas.Count, RngC.Areas.Count), 1 To 2)
  For Each Ar In RngB.Areas
    X = X + 1
    If Ar.Rows.Count = 1 Then
      Result(X, 1) = Ar
    Else
      Result(X, 1) = Join(Application.Transpose(Ar.Columns(1)), vbLf)
    End If
  Next
  X = 0
  For Each Ar In RngC.Areas
    X = X + 1
    If Ar.Rows.Count = 1 Then
      Result(X, 2) = Ar
    Else
      Result(X, 2) = Join(Application.Transpose(Ar.Columns(1)), vbLf)
    End If
  Next
  Range("B20").Resize(UBound(Result), 2) = Result
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Thanks alot for this. I think this will do the trick. I will check it out first thing in the morning.

for information, alternatively to VBA, you can use PowerQuery

I will have a look at powerquerry. Seems to be quite useful. Thanks for suggestion.