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)
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)