Which you don’t want to see in your data. Consider you got thousands of rows which are scrapped from Web or some other source which contains repeated unwanted text which you would like to remove to clean the data. I have developed a method which will do job for you.
RemoveUnwantedCodeVBAOVERALL(oRange, startChar, endChar)
- oRange: a mandatory parameter which is range type. User needs to pass range containing unwanted data.
- startChar: a mandatory parameter takes one character as starting point from where you would like to remove unwanted data from.
- endChar: a mandatory parameter takes one character as ending point up to that you would like to remove unwanted data.
Consider below data where I want to remove data which is blocked between “<“ and “>” as shown below:
Public Sub RemoveUnwantedCodeVBAOVERALL(oRange As Range, startChar As String, endChar As String) Dim oCell As Range For Each oCell In oRange.Cells Dim oStr As String If oCell.HasFormula = False Then oStr = oCell.Value Dim oSPosition As Long Dim oEPosition As Long oSPosition = InStr(1, oStr, startChar) If oSPosition > 0 Then oEPosition = InStr(1, oStr, endChar) If oEPosition > 0 Then Dim oReplaceStr As String oReplaceStr = Mid(oStr, oSPosition, (oEPosition - oSPosition) + 1) oCell = Replace(oStr, oReplaceStr, "") End If End If End If Next oCell 'Cleanup If Not oCell Is Nothing Then Set oCell = Nothing End If End Sub
Public Sub CallRemoveUnwantedText() Call RemoveUnwantedCodeVBAOVERALL(Selection, "<", ">") End Sub