Need to double click on a cell before VBA code changes applies

[ose]

Hi.

I have following Command Button which executed following code in order to remove empty line breaks from a given cell:

Private Sub CommandButton1_Click()
 Cells(24, "M").Select
Selection.Replace What:="" & Chr(10) & "" & Chr(10) & "", Replacement:="" & Chr(10) & ""
End Sub

The problem is that nothing happens when I execute the code. The changes from code only applies when:

1 - I manually double click on the cell ("Edit Mode")

2 - Clicking anywhere else to remove focus on the cell
3 - Reexecuting the code

The problem is that I manually need to double click on each cell to get it to work.
The cell is copied from a table that is loaded from external source.

 

[kevin9999]

See if the following gives you what you want (assumes M24 is on the active sheet when run)

Private Sub CommandButton1_Click()
    Cells(24, "M").Replace Chr(10) & Chr(10), Chr(10), xlPart
End Sub

 

[kevin9999]

This might be better...

Private Sub CommandButton1_Click()
    Dim i As Long: i = 2
    Do Until i < 2
        i = InStr(1, Range("M24"), Chr(10) & Chr(10))
        Range("M24").Replace Chr(10) & Chr(10), Chr(10), xlPart
    Loop
End Sub

 

[kevin9999]

Exceeded the time limit to edit; remove the Private, just:

Sub CommandButton1_Click()

 

[ose]

Thanks for your reply @kevin9999

I tested and verified that it works for a single cell, which is great ! .

I have tried to get it to work with multiple cells at once by extending the range functions to "M24:M28" but it did not work unfortunately:

Do you know how to apply this solution to multiple cells ?

 

[ose]

I managed to solve it @kevin9999.

Thanks a lot

 

[kevin9999]

Glad you got it working - well done!