Help needed on removing values

[Natsha]

I need an condition where if I want to remove value ;1-1, only that value should remove not ;1-1 in ;1-17, like wise I have many scenerio with different values...

Macro working fine...I have this bug which makes to not implementing this on my project.
Any help?

Code:
Sub Remove()

Dim answer As VbMsgBoxResult

answer = MsgBox("Are you sure you want to run the Macro?", vbYesNo, "Run Macro")

If answer = vbYes Then

Dim c As Range

svar = Application.InputBox("Insert keyword:", Title, Type:=2)

If svar = False Or svar = "" Then Exit Sub 'if user cancel

Selection.Replace What:=svar, Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _

MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

MsgBox "Task completed"

End If

End Sub

Also posted here

 

[Joe4]

If the values of ;1-1 that you want to replace only contain ;1-1 in those cells (and nothing else), then simply replace this part of your code:

LookAt:=xlPart

which allows for partial matches, with this:

LookAt:=xlWhole

which only looks for exact matches.

If that does not do what you want, please post some actual data, showing some values that should be replaced and some that should not be.

 

[Natsha]

If the values of ;1-1 that you want to replace only contain ;1-1 in those cells (and nothing else), then simply replace this part of your code:

LookAt:=xlPart

which allows for partial matches, with this:

LookAt:=xlWhole

which only looks for exact matches.

If that does not do what you want, please post some actual data, showing some values that should be replaced and some that should not be.

In column C I have datas like the below mentioned table ....in that for example I need to remove value ;1-1 but when use the macro it also remove ;1-1 present in ;1-17 ....which should not happen, another example if I want to remove ;1-2; but it also removes ;1-2 present in ;1-22 ... Hope u get my point , below I pasted my macro excel too.

1-88;1-8;1-17;1-1;1-2;1-22

1-88;1-8;1-17;1-1;1-2;1-22

The code I pasted above work's but if there is some conditions which can help this also means...it wil more helpful

Dropbox.. macro link

 

[Joe4]

That is going to be a bit tricky, because you then need to look at the values AFTER the last value.

You could choose to replace all instances of ;1-1; with ; which would replace all instances of it, except if it occurs at the very beginning or very end of a string.
So then you would also need some way of addressing those.

One way would be to temporarily add an ; to the beginning and end of every entry, then do the replace, then remove the ; from the beginning and end of every entry.

 

[Natsha]

That is going to be a bit tricky, because you then need to look at the values AFTER the last value.

You could choose to replace all instances of ;1-1; with ; which would replace all instances of it, except if it occurs at the very beginning or very end of a string.
So then you would also need some way of addressing those.

One way would be to temporarily add an ; to the beginning and end of every entry, then do the replace, then remove the ; from the beginning and end of every entry.

Okay I'll try it...

 

[Joe4]

Okay I'll try it...

Post back if you run into any issues.
All of this can be coded into the VBA, so people will not have to do it manually.

 

[Natsha]

That is going to be a bit tricky, because you then need to look at the values AFTER the last value.

You could choose to replace all instances of ;1-1; with ; which would replace all instances of it, except if it occurs at the very beginning or very end of a string.
So then you would also need some way of addressing those.

One way would be to temporarily add an ; to the beginning and end of every entry, then do the replace, then remove the ; from the beginning and end of every entry.

Post back if you run into any issues.
All of this can be coded into the VBA, so people will not have to do it manually.

As you suggested I have added ; to start and end but when remove ; it removes everything in the sheet...

I think my code not have option to remove prefix and suffix semicolon alone... It removes every semicolon in the sheet

Is there an solution?

 

[Marc L]

Is there an solution?

I can't see any according to your 'explanation' …​

But whatever the forum as it is very not difficult to well elaborate your need and to attach the expected result in order to see the light …​

​

 

[Joe4]

Natsha,

This is a little clunky, but it seems to do the job (maybe someone else will come up with a more streamlined solution, but if not, hopefully you at least have one that works).

Sub RemoveStrings()

    Dim answer As VbMsgBoxResult
    Dim sfind As Variant
    Dim srep As Variant
    Dim c As Range
    Dim cell As Range

    answer = MsgBox("Are you sure you want to run the Macro?", vbYesNo, "Run Macro")

    If answer = vbYes Then

'       Prompt on what to find
        sfind = Application.InputBox("Insert what to find:", Type:=2)
        If sfind = False Or sfind = "" Then Exit Sub 'if user cancel

'       Prompt on what to replace it with
        srep = Application.InputBox("Insert what to replace it with:", Type:=2)
        If srep = False Then Exit Sub 'if user cancel

        Set c = Selection
       
        Application.ScreenUpdating = False
       
'       Add semi-colons to beginning and end of every entry
        For Each cell In c
            cell.Value = ";" & cell.Value & ";"
        Next cell
       
'       Make replacements
        c.Replace What:=sfind, Replacement:=srep, LookAt:=xlPart, SearchOrder:=xlByRows, _
            MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
   
'       Run a second time to handle consecutive valus needing replacing
        c.Replace What:=sfind, Replacement:=srep, LookAt:=xlPart, SearchOrder:=xlByRows, _
            MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

'       Remove beginning and ending semi-colons
        For Each cell In c
            cell.Value = Mid(cell.Value, 2, Len(cell.Value) - 2)
        Next cell

        Application.ScreenUpdating = True

        MsgBox "Task completed"

    End If

End Sub

So, in your example, if you enter ;1-1; at the first prompt on what to replace, and you enter ; at the second prompt, on what to replace it with, I think it will do what you want.

 

[Natsha]

I can't see any according to your 'explanation' …​

But whatever the forum as it is very not difficult to well elaborate your need and to attach the expected result in order to see the light …​

​

Natsha,

This is a little clunky, but it seems to do the job (maybe someone else will come up with a more streamlined solution, but if not, hopefully you at least have one that works).

Sub RemoveStrings()

    Dim answer As VbMsgBoxResult
    Dim sfind As Variant
    Dim srep As Variant
    Dim c As Range
    Dim cell As Range

    answer = MsgBox("Are you sure you want to run the Macro?", vbYesNo, "Run Macro")

    If answer = vbYes Then

'       Prompt on what to find
        sfind = Application.InputBox("Insert what to find:", Type:=2)
        If sfind = False Or sfind = "" Then Exit Sub 'if user cancel

'       Prompt on what to replace it with
        srep = Application.InputBox("Insert what to replace it with:", Type:=2)
        If srep = False Then Exit Sub 'if user cancel

        Set c = Selection
    
        Application.ScreenUpdating = False
    
'       Add semi-colons to beginning and end of every entry
        For Each cell In c
            cell.Value = ";" & cell.Value & ";"
        Next cell
    
'       Make replacements
        c.Replace What:=sfind, Replacement:=srep, LookAt:=xlPart, SearchOrder:=xlByRows, _
            MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
 
'       Run a second time to handle consecutive valus needing replacing
        c.Replace What:=sfind, Replacement:=srep, LookAt:=xlPart, SearchOrder:=xlByRows, _
            MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

'       Remove beginning and ending semi-colons
        For Each cell In c
            cell.Value = Mid(cell.Value, 2, Len(cell.Value) - 2)
        Next cell

        Application.ScreenUpdating = True

        MsgBox "Task completed"

    End If

End Sub

So, in your example, if you enter ;1-1; at the first prompt on what to replace, and you enter ; at the second prompt, on what to replace it with, I think it will do what you want.

My apologies...I didn't read the comments well..it actually works well... thank you so much for not giving up on me...