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]

Thanks for helping me...but it's doesn't work I don't have anything to replace..I just want to remove value , your solution on adding ; to start and end is really good...but I dnt an code to add and remove semicolon of only the prefix and suffix of it..

Can you just try my code, following the EXACT steps that I gave you here?

So, in your example, if you enter ;1-1; at the first prompt on what to replace, and you enter ; at the second prompt

I think that should do exactly what you want.

I tested it out that had those values at the beginning, in the middle, and at the end, and it seems to remove all the instances of 1-1 correctly, and nothing else.

Here is a screen print of the entries I tried and the results (all the values that were removed are in red font).

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

 

[Natsha]

Can you just try my code, following the EXACT steps that I gave you here?


I think that should do exactly what you want.

I tested it out that had those values at the beginning, in the middle, and at the end, and it seems to remove all the instances of 1-1 correctly, and nothing else.

Here is a screen print of the entries I tried and the results.

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

Yes it's working...I didn't read the comments well... U saved my day... thank you so much Joe... For not giving up on me.

 

[Joe4]

You are welcome.

Basically, the logic to follow is to replace the value you want, surrounded by the value separator (semi-colon) on each side of it, with exactly one instance of the value separator (as if we are removing 2, we need to replace 1).

 

[Joe4]

Note: If you are creating this for other users to user, and want to make it a little more "user friendly" (as those previous instructions may be a little confusing to users), we could simplify it by simply asking them what value they want to remove (1-1) and what the value separator is (semi-colon).
Then we could build the entire value to search for ( ;1-1; ) right in the code, like this:

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 the value you want to remove:", Type:=2)
        If sfind = False Or sfind = "" Then Exit Sub 'if user cancel

'       Prompt on what to replace it with
        srep = Application.InputBox("Insert the value separator:", 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:=srep & sfind & srep, 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:=srep & sfind & srep, 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

Or, if you know that the value separator will ALWAYS be a semi-colon, then you could just hard-code that part into the code and remove that second prompt altogether.

 

[Joe4]

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 …

It was pretty clear to me after they posted the examples in post 3.
Maybe you missed that or just didn't read it carefully enough.

 

[Marc L]

Sorry I'm a very beginner to guess as again his 'explanation' neither the attachment well describe the need​

- as it's at kid level to well elaborate a need and to attach a before state and the expected result -​

the reason why on several forums nobody answer except you but after some posts …​

 

[Joe4]

​

Sorry I'm a very beginner to guess as again his 'explanation' neither the attachment well describe the need​

- as it's at kid level to well elaborate a need and to attach a before state and the expected result -​

the reason why on several forums nobody answer except you but after some posts …​

Odd, I thought it was "kid-level" to understand what they wanted after the second post that included the example.
Seemed pretty straightforward to me, no "guessing" needed at that point.
Sometimes you just got to read it carefully, and read the posts together to get a clear understanding.

 

[Natsha]

This one is perfect...thank you

 

[Joe4]

You are welcome.

 

[Peter_SSs]

I need an condition where if I want to remove value ;1-1, only that value should remove not ;1-1 in ;1-17,

Interesting that you asked this, as it is exactly the scenario I was raising in your other thread here but you dismissed it as not possible.