Aligment of specific text. text

[Panoos64]

All hi, i would like to create a VBA code so that to run through col. "H" and where find a cell which contains the below break text, should make it to one line. Therefore that the break text is in same cell. In my below extract the "H" is the col. and "12" is the row. I present below the original data and expected result.

Thank you all in advance


Original data

H
12 Welfare Fund Summary Totals
Employer 1


Expected result

H
12 Welfare Fund Summary Totals Employer 1

 

[Fluff]

If those cells contain a standard line break, then simply select the column
Ctrl H in the find what box enter Ctrl J
Leave the replace box empty & click ok

 

[RCBricker]

All hi, i would like to create a VBA code so that to run through col. "H" and where find a cell which contains the below break text, should make it to one line. Therefore that the break text is in same cell. In my below extract the "H" is the col. and "12" is the row. I present below the original data and expected result.

Thank you all in advance


Original data

H
12 Welfare Fund Summary Totals
Employer 1


Expected result

H
12 Welfare Fund Summary Totals Employer 1

Here is code to check each cell in the range and remove the line break.

Sub Panoos64()

Dim wb As Workbook
Dim ws As Worksheet
Dim str As String
Dim lngROW As Long, lngCOL As Long
Dim rng As Range, cell As Range
Dim intCOL As Integer


Set wb = ThisWorkbook
Set ws = wb.Sheets("Panoos64")  'change sheet name to your sheet name
Set cell = Application.InputBox("Select the range to process", Type:=8)
intCOL = cell.Column

With ws
lngROW = ws.Cells.Find(What:="*", _
        after:=ws.Cells(1), _
        LookAt:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Row
lngCOL = ws.Cells.Find(What:="*", _
        after:=ws.Cells(1), _
        LookAt:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Column
Set rng = ws.Range(ws.Cells(1, intCOL), ws.Cells(lngROW, intCOL))
For Each cell In rng
On Error Resume Next
    cell.Value = Replace(cell.Value, vbLf, " ")
On Error GoTo 0
Next cell
        
End With


End Sub

 

[Panoos64]

RCBric, i would like to thank you. your code works but with it appears "Input Box" in order to choose the column. However the column is the "H" and so is it possible to modify it so that to get it by default without use the "Input Box"? Thank you for your support . Hv a nice day

 

[Michael M]

Try

Sub MM1()
Dim lr As Long
lr = Cells(Rows.Count, "H").End(xlUp).Row
For Each cell In Range("H1:H" & lr)
On Error Resume Next
    cell.Value = Replace(cell.Value, vbLf, " ")
On Error GoTo 0
Next cell
End Sub

 

[Panoos64]

Hi Fluf, i followed your above instructions but it doesn't work. Is it possible by VBA code? Thank you once again for your support

 

[Panoos64]

Hi Michael, is perfect work! Thank you for continuance support. Hv a great day

 

[Michael M]

Glad to help...