VBA for formatting line breaks

afrobea_r

Board Regular
Joined
Aug 16, 2015
Messages
76
Dear Excel Community,

Currently I have multiple cells with data arranged like the example below:
Apple

Orange

Watermelon & Mangosteen


Using this formula :

For Each UsedCell In Selection

Cells.Replace What:=Chr(10), Replacement:=" "

My data gets rearranged to:
AppleOrangeWatermelon &Mangosteen

How can I modify the formula to arrange data in this manner instead.

Apple
Orange
Watermelon & Mangosteen


<tbody>
</tbody>

Essentially I would like to retain the existing paragraphing format but without the level spaces.

Help appreciated!

Thank you!
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
Try:

Code:
Selection.Replace What:=Chr(10) & Chr(10), Replacement:=Chr(10), LookAt:=xlPart
 
Last edited:

afrobea_r

Board Regular
Joined
Aug 16, 2015
Messages
76
Try:

Code:
Selection.Replace What:=Chr(10) & Chr(10), Replacement:=Chr(10), LookAt:=xlPart


Btw Sir,

If I wish to combine the conditional formatting based on values assigned to adjacent cells, is this correct?

Sub CombinedFormat()
' test Macro
With Range("L12").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=3


If Range("Q3") = "2" Then


Cells.Replace What:=Chr(10) & Chr(10), Replacement:=Chr(10), LookAt:=xlPart


End If


If Range("Q3") = "1" Then


Cells.Replace What:=Chr(10), Replacement:=" "


End If


End With


End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,393
Messages
5,528,477
Members
409,818
Latest member
JiNXX9500

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top