how to replace the "Alt-Enter" character in a cell with sway "@"

Hi,

Maybe this:

Sub DeleteALTENTER()

Dim Rng As Range
Dim LRow As Long

LRow = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range("A1:A" & LRow)

    For Each r In Rng
        
        If InStr(r.Value, Chr(10)) > 0 Then
            
            r.Value = Replace(r.Value, Chr(10), "@")
            
        End If
        
    Next r
    
End Sub

Regards

Hi,

I'm afraid I'm still a bit of an amateur when it comes to VBA so I couldn't comment on the perfomance difference between those two methods.

Hopefully someone with a bit of expertise will pick up on this thread.

Cheers

UsedRange can be unreliable as Excel "remembers" how many rows there have been. If you add and delete rows, Excel retains the highest value. Same with columns.

So with UsedRange you may process more rows than actually have data.

If you use End(xlUp) with a column that is fully populated, like a key field, it will locate the true last row.


Regards, TMS

You may also try:

range("A:A").Replace what:=chr(10), replacement:="@", lookat:=xlpart

@Izandol Thanks for doing in one line of code what took me a dozen!!

@Trevor Thanks for pointing that out.

@JMac Appears we share the same level of stuck-in-For-Next-looping solutions!!

Cheers

I assume that multiple cols can be handled by changing the "A:A" to say "A:B" and that I could just change the "what" and "replacement" values so say, replace every space with "@"

Yes, and yes.

And, for the question you haven't yet asked: you can change xlPart to xlWhole if you want to check only the entire contents of the cells rather than individual characters or words.

Thanks for the rep

Regards, TMS

Also you may replace cell format.