Target specific data string to delete

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Target specific data string to delete

Post by bradjedis »

Greetings,

I have an exceeding large file, and am needing to delete certain data from a column of data.

I would like to build a list of the specific string of data to delete, have the VBA read that list, and structure down the column and remove that specific string, and then proceed to the next item in the list, etc...

ex:

Col D contains alphanumeric data

the cells would have something like 123tfabced3323reva or 123tfabced3323revab

I would build a list on another tab

reva
revaa
revab
revac
etc..

VB should search col D for the exact string and delete every occurrence.

Thoughts?

Thanks,
BRad

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Target specific data string to delete

Post by HansV »

So 123tfabced3323reva should become 123tfabced3323 ?
Best wishes,
Hans

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Target specific data string to delete

Post by bradjedis »

Correct

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Target specific data string to delete

Post by HansV »

You'll have to build the list from longest to shortest, for if we would first remove "reva", we wouldn't find "revab" anymore.
So:

revaa
revab
revac
reva

The order or revaa, revab, revac doesn't matter - they don't bite each other. But they must come before reva.

Here is a macro. Change the worksheet names as needed. I have assumed that the list of strings on the second sheet is in column A, starting in A1. You can change this in the code.

Code: Select all

Sub RemoveStrings()
    Dim wsh1 As Worksheet
    Dim wsh2 As Worksheet
    Dim rng As Range
    Dim cel As Range
    Application.ScreenUpdating = False
    Set wsh1 = Worksheets("Sheet1") ' Sheet with data
    Set wsh2 = Worksheets("Sheet2") ' Sheet with list of strings to remove
    Set rng = wsh2.Range(wsh2.Range("A1"), wsh2.Range("A1").End(xlDown))
    For Each cel In rng
        wsh1.Range("D:D").Replace What:=cel.Value, Replacement:="", LookAt:=xlPart
    Next cel
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Target specific data string to delete

Post by bradjedis »

Just got back to this... Works great! Thanks