VBA - How to invert/sort line breaks within a cell

Luthius

Active Member
Joined
Apr 5, 2011
Messages
324
Guys, how can we change the line breaks order within a cell?
For instance, in my cell I have the values:

A1 Cell
1st Comment
2nd Comment
3rd Comment

<tbody>
</tbody>


I want to invert to:
3rd Comment
2nd Comment
1st Comment

<tbody>
</tbody>

For a more complex case, how to Sort based on the first character of the line?

A1 Cell
Watermelon
Apple
Papaia
Strawberry

<tbody>
</tbody>

Sorted values in the A1 Cell
Apple
Papaia
Strawberry
Watermelon

<tbody>
</tbody>
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
For your first question, this code will invert the lines of text within the cell that is selected at the time it is run...
Code:
Sub InvertWithinSelectedCell()
  Dim X As Long, ArrIn As Variant, ArrOut As Variant
  ArrIn = Split([B][COLOR="#0000FF"]ActiveCell[/COLOR][/B].Value, vbLf)
  ReDim ArrOut(0 To UBound(ArrIn))
  For X = 0 To UBound(ArrIn)
    ArrOut(UBound(ArrIn) - X) = ArrIn(X)
  Next
  [B][COLOR="#0000FF"]ActiveCell [/COLOR][/B]= Join(ArrOut, vbLf)
End Sub
If you really meant you only wanted to do this to cell A1, change the highlighted ActiveCell text to Range("A1").
 
Last edited:
Upvote 0
Amazing solution. Thank you very much

Mr Rick and also for the the second solution Mr CalcSux78.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top