VBA - If a cell ends with more than one char(10), replace it with only one char (10)

[LearnNewThings]

I have a column with multiple line text, some of which end with multiple line breaks (char(10)). I want to be able to search the entire column and replace the cells that end with multiple char(10) and replace it with only one char(10). I would like to perform this using VBA and include it within the project I already have ongoing.

Details on the data (small sample of scenarios below):
- Row 1 has 3 lines of text with >1 char(10) at the end of the data
- Row 2 has 2 lines of text with 1 char(10) at the end (this is how they all should be)
- Row 3 has 1 line of text with >1 char(10)

Book1
	A
1	This is line 1 This is line 2 This is line 3
2	This is another 1 This is another 2
3	Yet one more
Sheet1

 

[Joe4]

Try this:

Sub MyReplaceMacro()
    Cells.Replace What:="" & Chr(10) & "" & Chr(10) & "", Replacement:="" & Chr(10) & "", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

 

[LearnNewThings]

Try this:

This worked out perfectly, thank you!!

 

[Joe4]

You are welcome.
Glad I was able to help!