Removing Alternate line Breaks

[Fen_Tiger]

Hi, hope someone can help me. I'm trying to write a VBA macro to remove alternate line breaks (alt+0010 or alt+enter) from a number of cells. The worksheet is output from a risk register and I need to tidy it up before presenting to senior managers. The cells look like this:

- text1(alt+0010)
(alt+0010)
- text2(alt+0010)

By removing the alternate line breaks I want to shift the text up to look like this:

- text1(alt+0010)
- text2(alt+0010)

I'm very new to writing VBA code, but I realise I can use the Substitute worksheetfunction and a for next loop, but it appears to do nothing. Can anyone give me any advice. The code I've written so far is:

Dim Result As String
Dim b As Long
With Selection
For b = 2 To 20 Step 2 (there aren't any more than 10 instances per cell)
Result = Application.WorksheetFunction.Substitute(ActiveCell, " & Chr(10) & ", "", b)
Next b
End With
Range("P3").Value = Result

I know it still needs some work to do the whole document, but one step at a time!
Any help gratefully received.

David

 

[gsbelbin]

Hi, hope someone can help me. I'm trying to write a VBA macro to remove alternate line breaks (alt+0010 or alt+enter) from a number of cells. The worksheet is output from a risk register and I need to tidy it up before presenting to senior managers. The cells look like this:

- text1(alt+0010)
(alt+0010)
- text2(alt+0010)

By removing the alternate line breaks I want to shift the text up to look like this:

- text1(alt+0010)
- text2(alt+0010)

I'm very new to writing VBA code, but I realise I can use the Substitute worksheetfunction and a for next loop, but it appears to do nothing. Can anyone give me any advice. The code I've written so far is:

Dim Result As String
Dim b As Long
With Selection
For b = 2 To 20 Step 2 (there aren't any more than 10 instances per cell)
Result = Application.WorksheetFunction.Substitute(ActiveCell, " & Chr(10) & ", "", b)
Next b
End With
Range("P3").Value = Result

I know it still needs some work to do the whole document, but one step at a time!
Any help gratefully received.

David

Hi David
If I'm reading this correctly then all you want to do is replace every instance of two carriage returns with one. So for example for the cell you used above just use the line

x=ActiveSheet.Cells(3,16).Replace(Chr(10) & Chr(10), Chr(10))

Hopefully this will work

Cheers

Gordon

 

[gsbelbin]

Hi David
Had a bit more of think about this. If you want to remove these from every cell in the sheet you could use

For Each cell In ActiveSheet.UsedRange

x = cell.Replace(Chr(10) & Chr(10), Chr(10))

Next cell


Cheers

Gordon

 

[Fen_Tiger]

Genius!

Many thanks Gordon

D