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
Target specific data string to delete
-
- 4StarLounger
- Posts: 536
- Joined: 30 Mar 2010, 18:49
- Location: United States
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Target specific data string to delete
So 123tfabced3323reva should become 123tfabced3323 ?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 536
- Joined: 30 Mar 2010, 18:49
- Location: United States
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Target specific data string to delete
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.
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
Hans
-
- 4StarLounger
- Posts: 536
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Target specific data string to delete
Just got back to this... Works great! Thanks