concatenated text in a word wrapped cell with line breaks/feeds

[cjcass]

Hi,
I have the code below that adds line breaks/feeds to concatenated text in a word wrapped cell so each bit of text in the concatenation is on its own line in the cell.
The problem is that not all cells will have text in them - my code still adds the Chr(10) line breaks so I end up with a large quantity of redundant line breaks in my result - how can I get round this so my code only concatenates line breaks after a cell with a value in it? My cells will allways have values from AA9 down to a point, eg. AA9:AA20, or AA9:AA28 etc.
Any help much appreciated.

Range("AB9").Value = Range("AA9").Value & Chr(10) & Range("AA10").Value & Chr(10) & _
Range("AA11").Value & Chr(10) & Range("AA12").Value & Chr(10) & _
Range("AA13").Value & Chr(10) & Range("AA14").Value & Chr(10) & _
Range("AA15").Value & Chr(10) & Range("AA16").Value & Chr(10) & _
Range("AA17").Value & Chr(10) & Range("AA18").Value & Chr(10) & _
Range("AA19").Value & Chr(10) & Range("AA20").Value & Chr(10) & _
Range("AA21").Value & Chr(10) & Range("AA22").Value & Chr(10) & _
Range("AA23").Value & Chr(10) & Range("AA24").Value & Chr(10) & _
Range("AA25").Value & Chr(10) & Range("AA26").Value & Chr(10) & _
Range("AA27").Value & Chr(10) & Range("AA28").Value & Chr(10) & _
Range("AA29").Value & Chr(10) & Range("AA30").Value & Chr(10) & _
Range("AA31").Value & Chr(10) & Range("AA32").Value & Chr(10) & _
Range("AA33").Value & Chr(10) & Range("AA34").Value & Chr(10) & _
Range("AA35").Value & Chr(10) & Range("AA36").Value & Chr(10) & _
Range("AA37").Value & Chr(10) & Range("AA38").Value & Chr(10) & _
Range("AA39").Value & Chr(10) & Range("AA40").Value & Chr(10) & _
Range("AA41").Value & Chr(10) & Range("AA42").Value & Chr(10) & _
Range("AA43").Value & Chr(10) & Range("AA44").Value & Chr(10) & _
Range("AA45").Value & Chr(10) & Range("AA46").Value & Chr(10) & _
Range("AA47").Value & Chr(10) & Range("AA48").Value & Chr(10) & _
Range("AA49").Value & Chr(10) & Range("AA50").Value & Chr(10) & _
Range("AA51").Value & Chr(10) & Range("AA52").Value & Chr(10)

 

[Fluff]

How about

   Dim Tmp As Variant, ary() As Variant
   Dim i As Long, j As Long
   
   Tmp = Application.Transpose(Range("AA9", Range("AA" & Rows.Count).End(xlUp)))
   For i = LBound(Tmp) To UBound(Tmp)
      If Tmp(i) <> "" Then
         j = j + 1
         ReDim Preserve ary(1 To j)
         ary(j) = Tmp(i)
      End If
   Next i
   Range("AB9").Value = Join(ary, Chr(10))

 

[cjcass]

Brilliant!
works great, thanks a lot for this!!

 

[Fluff]

Glad to help & thanks for the feedback