Help needed on removing values

Natsha

New Member
Joined
May 20, 2021
Messages
44
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
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
 
BTW, if you were interested in a different approach, you could try this.

VBA Code:
Sub Del_Text()
  Dim RX As Object
  Dim c As Range
  Dim sWhat As String
  
  Const sep As String = ";"
  
  sWhat = InputBox("What do you want to delete?")
  Application.ScreenUpdating = True
  If Len(sWhat) > 0 Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.Pattern = sep & sWhat & "(?=" & sep & "|$)"
    For Each c In Selection
      c.Value = Mid(RX.Replace(sep & c.Value, ""), 2)
    Next c
  End If
  Application.ScreenUpdating = False
End Sub
 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
BTW, if you were interested in a different approach, you could try this.

VBA Code:
Sub Del_Text()
  Dim RX As Object
  Dim c As Range
  Dim sWhat As String
 
  Const sep As String = ";"
 
  sWhat = InputBox("What do you want to delete?")
  Application.ScreenUpdating = True
  If Len(sWhat) > 0 Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.Pattern = sep & sWhat & "(?=" & sep & "|$)"
    For Each c In Selection
      c.Value = Mid(RX.Replace(sep & c.Value, ""), 2)
    Next c
  End If
  Application.ScreenUpdating = False
End Sub
Thanks Peter...I'll definitely try it ?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top