delete words from cellls

finnstone
finnstone used Ask the Experts™
on
i have a blacklist of words i need to be deleted, 400 words

in another book, col A, I need to delete a word from a list in a cell where those words in are the cells. it is not a simple vlookup since col A format is lists of words sepeareted by commas

so
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
If you could upload a sample workbook that would be helpful.


»bp

Author

Commented:
here it is
Book3.xlsx
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Sub RemoveBlacklisted()
Dim lngLastRow As Long
Dim lngRow As Long
Dim strWords() As String
Dim lngIndex As Long
Dim lngNextRow As Long
Dim wsBL As Worksheet
Dim rngFound As Range
Const DATA_COL = "A" ' Change as needed
Const FIRST_ROW = 1 ' Change as needed

Set wsBL = Sheets("blacklist")
lngLastRow = Range(DATA_COL & "1048576").End(xlUp).Row
lngNextRow = FIRST_ROW

For lngRow = FIRST_ROW To lngLastRow
    strWords = Split(Cells(lngRow, DATA_COL), ",")
    Cells(lngRow, DATA_COL).ClearContents
    For lngIndex = 0 To UBound(strWords)
        If strWords(lngIndex) = "" Then
            strWords(lngIndex) = " "
        End If
        Set rngFound = wsBL.Columns("A:A").Find(What:=Trim(strWords(lngIndex)), After:=ActiveCell)
        If rngFound Is Nothing Then
            If strWords(lngIndex) = " " Then
                strWords(lngIndex) = ""
            End If
            If lngIndex < UBound(strWords) Then
                Cells(lngRow, DATA_COL) = Cells(lngRow, DATA_COL) & Trim(strWords(lngIndex)) & ","
            Else
                Cells(lngRow, DATA_COL) = Cells(lngRow, DATA_COL) & Trim(strWords(lngIndex))
            End If
        End If
    Next
Next
End Sub

Open in new window

Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You’re welcome and I’m glad I was able to help.

And thank you for my 5 millionth point!

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017

Author

Commented:
thanks. wow lots of points
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
There are 124 Experts with more points than I. The leader is Guy Hengel with 41 million!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial