Fixing my .selection code

[Laurence D]

Hey all,

Can somebody spend a minute or so to fix my code that I cannot get working? I am trying to increase the flexibility of this code rather than just setting the range it will be based on my selection in the active sheet. Do I have to loop this each cell in the selection to get this working?

Sub Remove_slashes()
Dim the_string As String


the_string = ActiveSheet.Selection
   
   the_string = Replace(the_string, "/", "")
    
    ActiveSheet.Selection = the_string


End Sub

Cheers,
Laurence

 

[Joe4]

Try this:

Sub Remove_slashes()


    On Error Resume Next
    
    Selection.Replace What:="/", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    On Error GoTo 0
    
End Sub

 

[My Aswer Is This]

Try this:

[LEFT][COLOR=#222222][FONT=Tahoma]Sub Remove_slashes()[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]'Modified  12/5/2018  4:05:12 PM  EST[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]Dim the_string As String[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]the_string = Selection.Value[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]the_string = Replace(the_string, "/", "")[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]Selection.Value = the_string[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]End Sub[/FONT][/COLOR][/LEFT]

 

[Joe4]

Try this:

Sub Remove_slashes()
'Modified 12/5/2018 4:05:12 PM EST
Dim the_string As String
the_string = Selection.Value
the_string = Replace(the_string, "/", "")
Selection.Value = the_string
End Sub

That only works if the selection is exactly one cell. If it is a multi-cell selection, the code throws errors.

Based on this line here:

Do I have to loop this each cell in the selection to get this working?

I am pretty sure that they want it to work on a multi-cell selected range (which my code will do).

 

[Laurence D]

Thanks Joe! You are a legend.

 

[My Aswer Is This]

If your selection is for more then one cell then use this:

Sub Remove_slashes()
'Modified  12/5/2018  4:22:12 PM  EST
Dim r As Range
    For Each r In Selection
        r = Replace(r.Value, "/", "")
    Next
End Sub

 

[Joe4]

You are welcome.

There is no reason to use a loop here, as it can be done without loops.
Generally, you want to avoid using loops whenever possible, as they use more resources, and thus are slower and less efficient (there are times when you can't avoid using them, but many times you can!).