How to delete particular value in a cell without deleting existing data using find option

[Natsha]

I need an macro to delete the value which I find ...Example , I have data in coloum A and B, with header and data start in A2 and B2 and extend .
Starting from column B2 ,I have values like 1-6;3-8;12-6 ..etc. I eed an macro where I can remove particular value I type in textbox by find option and deleted it with deleting other data in the cella
Example B2 has 1-6;3-8;12-6;9-9 ,I want to remove ;9-9, after I paste the value in textbox it should remove ;9-9 and change into 1-6;3-8;12-6;

I Need macro where I can select the cells that datas need to be removed. Column B is the default column containing datas

Note: The value I need to delete changes everytime like, ;1-1 or 8-9; or 12-13;

kindly help.

 

[Peter_SSs]

Example B2 has 1-6;3-8;12-6;9-9 ,I want to remove ;9-9, after I paste the value in textbox it should remove ;9-9 and change into 1-6;3-8;12-6;

Your example is not entirely clear. If ";9-9" is removed then there would be no semicolon remaining at the right end of the text.

To help clarify different possibilities, what should be left in the cell if it starts with the following and you put ";9-9" in the textbox?
9-9;5-4;9-9;8-4;9-91;9-9

You could also give some more examples of your own if you think that it would help clarify the requirement.

 

[Natsha]

Your example is not entirely clear. If ";9-9" is removed then there would be no semicolon remaining at the right end of the text.

To help clarify different possibilities, what should be left in the cell if it starts with the following and you put ";9-9" in the textbox?
9-9;5-4;9-9;8-4;9-91;9-9

You could also give some more examples of your own if you think that it would help clarify the requirement.

Hi

Thanks for the reply

To make it clear, in a cell there is a data like this.. 1-1;2-2;3-3;4-4

If I want to remove 4-4 , I do find and replace..in find box I give ;4-4 and leave the replace box blank... Along 4-4 I need to the semicolon also get deleted.

In another example if I want to remove 1-1 , in find box I give 1-1; and in replace box blank so that 1-1 will get deleted along with the unwanted semicolon.


I need a macro .where which ever value I want to remove ,I can text it in the textbox or msg box and that value should be removed in the excel sheet...all data will be from b column starting from b2

 

[Tetra201]

See if the following works for you:

Sub Test()
    Dim str As String, rng As Range
    str = ";" & InputBox("Enter text to be removed")
    If str = ";" Then Exit Sub
    Set rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
    Application.ScreenUpdating = False
    rng = Evaluate(""";""&" & rng.Address)
    rng.Replace str, "", xlPart, xlByRows, False
    rng = Evaluate("INDEX(REPLACE(" & rng.Address & ",1,1,""""),,)")
    Application.ScreenUpdating = True
End Sub

 

[Natsha]

See if the following works for you:

Sub Test()
    Dim str As String, rng As Range
    str = ";" & InputBox("Enter text to be removed")
    If str = ";" Then Exit Sub
    Set rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
    Application.ScreenUpdating = False
    rng = Evaluate(""";""&" & rng.Address)
    rng.Replace str, "", xlPart, xlByRows, False
    rng = Evaluate("INDEX(REPLACE(" & rng.Address & ",1,1,""""),,)")
    Application.ScreenUpdating = True
End Sub

Hi it works but it's only removing values like 1-1; if it is the first value of the cell.. it's not removing values like this ;2-2... If the semicolon is after the value it's removes but if its before the value it's not removing, also it's not removing if the value is pasted inbetween.

I need an code where it should remove value like 1-1; or ;2-3 .. wherever it positioned... The value which I need to delete sometimes will be pasted first sometimes middle ,sometimes at the end.

 

[Tetra201]

The code offered in Post #4 has been tested to remove the required patterns located at the beginning, in the middle, and at the end of a text string.

Try the following modification -- it provides a better guidance on what needs to be entered.

Sub Test()
    Dim str As String, rng As Range
    Do: str = ";" & InputBox("Enter text to be removed. Example: 1-2")
    Loop Until str Like ";#-#" Or str Like ";#-##" Or str Like ";##-#" Or str Like ";##-##" Or str = ";"
    If str = ";" Then Exit Sub
    Set rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
    Application.ScreenUpdating = False
    rng = Evaluate(""";""&" & rng.Address)
    rng.Replace str, "", xlPart, xlByRows, False
    rng = Evaluate("INDEX(REPLACE(" & rng.Address & ",1,1,""""),,)")
    Application.ScreenUpdating = True
End Sub

 

[Natsha]

The code offered in Post #4 has been tested to remove the required patterns located at the beginning, in the middle, and at the end of a text string.

Try the following modification -- it provides a better guidance on what needs to be entered.

Sub Test()
    Dim str As String, rng As Range
    Do: str = ";" & InputBox("Enter text to be removed. Example: 1-2")
    Loop Until str Like ";#-#" Or str Like ";#-##" Or str Like ";##-#" Or str Like ";##-##" Or str = ";"
    If str = ";" Then Exit Sub
    Set rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
    Application.ScreenUpdating = False
    rng = Evaluate(""";""&" & rng.Address)
    rng.Replace str, "", xlPart, xlByRows, False
    rng = Evaluate("INDEX(REPLACE(" & rng.Address & ",1,1,""""),,)")
    Application.ScreenUpdating = True
End Sub

Sorry, if didn't work it only delete if the value I need to delete is the first one.

To make it more clear
In a cell b2 ..I have data like 20-98; 6-3; 7-7;1-94
If I need to delete 20-98; it's deleting it ,but If I want to delete 6-3; an middle value or ;1-94 last value it's not deleting

 

[Akuini]

Need macro where I can select the cells that datas need to be removed. Column B is the default column containing datas

Edited:
Select the range, run the code, type the keyword in the inputbox then OK.

Sub a1183689a()

Dim c As Range
Dim sAddress As String, svar
   
    svar = Application.InputBox("Insert keyword:", Title, Type:=2)
   
    If svar = False Or svar = "" Then Exit Sub 'if user cancel
    With Selection
    Set c = .Find(What:=svar, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not c Is Nothing Then
        sAddress = c.Address
        Do
           Set c = .FindNext(c)
           c = Replace(c, svar, "")
        Loop While Not c Is Nothing And c.Address <> sAddress
    Else
        MsgBox svar & " is not found"
    End If
    End With

End Sub

 

[Natsha]

Edited:
Select the range, run the code, type the keyword in the inputbox then OK.

Sub a1183689a()

Dim c As Range
Dim sAddress As String, svar
  
    svar = Application.InputBox("Insert keyword:", Title, Type:=2)
  
    If svar = False Or svar = "" Then Exit Sub 'if user cancel
    With Selection
    Set c = .Find(What:=svar, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not c Is Nothing Then
        sAddress = c.Address
        Do
           Set c = .FindNext(c)
           c = Replace(c, svar, "")
        Loop While Not c Is Nothing And c.Address <> sAddress
    Else
        MsgBox svar & " is not found"
    End If
    End With

End Sub

Works perfectly... thank you so much ?

 

[Akuini]

Sorry, actually it could be simpler:

Sub a1183689b()

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

End Sub