Can I remove 100 characters that are duplicate and found in other cells?

[sorovince]

I apologize if this has been asked before but I couldn't find it.


I want to be able to tell excel to remove at least 100 duplicate characters that are found in other cells. I uploaded my excel here so you can see what I mean:


https://www.dropbox.com/s/e5i96f58o3...test.xlsx?dl=0


So, these 100 characters...


.navheader { display: block; width: 120px; font-family: Georgia; font-size: 16px; font-weight: bold;


...are found in B2, B3, and B4. Is there a way to click on a button or execute a macro so that each time excel finds 100+ characters that are duplicate, to delete them, leaving only one copy of it?


Let me know in case I'm doing a bad job of explaining it.


Thank you so much,
Trajko

[Logit]

.
This macro will search the entire workbook and delete any term / terms you designate. In this example the macro
is seeking a double forward slash and replacing it with a single forward slash.

Sub Removediag()    Dim sht As Worksheet
    For Each sht In ActiveWorkbook.Worksheets
        sht.UsedRange.Replace "//", "/", xlPart
    Next
End Sub

[sorovince]

Hi Logit,

Thank you for replying. While that is certainly a handy tip, it doesn't solve the issue that I have. I need a macro that searches for a set of characters (let's say 100 characters) that are appearing multiple times in different cells. I apologize if i haven't been clear in my original description

[Logit]

If you specify in the macro, the "hundred characters" you want removed, the macro will do that for you.

Replace the FORWARD SLASH symbols with the hundred characters. Replace the SINGLE FORWARD SLASH symbol with "" (means you don't want the characters
replaced with anything ... you simply want them deleted.

Example : sht.UsedRange.Replace "abcdefghijk etc etc", "", xlPart

[sorovince]

Yes, that would be a great solution if I had a specific 100+ characters that I want to be removed. But I want any set of characters. For for example if...

asdfkljasdf;lkjasasdklfja;sldkjfasdl;fkjasdlkfjaslkdfj;alsdf;lakjsdfl;kasjdflkajsdf;aljsdfafsdlk;jasdlkfjasdlfkjasdlfkj

and....

32498324987329487324098723049872394872309872409817309821730981273098127389127301928370912873

are both found multiple times in multiple cells, they should both be deleted, because they are both duplicates. In other words, I don't to specify a specific set of characters but rather tell excel to delete everything that it finds that is a duplicate. And what is a duplicate? It's a string of at least 100 characters that is found more than once across all cells.

Again, feel free to let me know Logit if I'm doing a bad job explaining this, as I'm fairly new to this.

Thanks again for your help.

[Logit]

.
My fault ... your explanation is accurate.

Try this and let me know :

Option Explicit


Sub DeleteDupsInRows()
     
     
    Dim DataRow As Variant
    Dim Dict    As Object
    Dim j       As Long
    Dim k       As Long
    Dim Key     As String
    Dim lastCol As Long
    Dim lastRow As Long
    Dim Rng     As Range
    Dim Wks     As Worksheet
     
    For Each sht In ActiveWorkbook.Worksheets
    
        Set Wks = ActiveSheet
         
        Set Rng = Wks.Range("A1")
         
        lastCol = Wks.Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False, False, False).Column
        lastRow = Wks.Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False, False, False).Row
         
        If lastRow < Rng.Row Then Exit Sub
         
        Set Rng = Rng.Resize(lastRow - Rng.Row + 1, lastCol - Rng.Column + 1)
         
        Set Dict = CreateObject("Scripting.Dictionary")
        Dict.CompareMode = vbTextCompare
         
        For j = 1 To Rng.Rows.Count
            DataRow = Rng.Rows(j).Value
            For k = 1 To UBound(DataRow, 2)
                Key = Trim(DataRow(1, k))
                If Key <> "" Then
                    If Not Dict.Exists(Key) Then
                        Dict.Add Key, 1
                    End If
                End If
            Next k
            Rng.Rows(j).Value = Empty
            Rng.Rows(j).Resize(1, Dict.Count).Value = Dict.Keys
            Dict.RemoveAll
        Next j
    Next
End Sub

[sorovince]

Hey Logit,

When I run the macro I get a message saying...

Compile error:

Variable not defined

You can see printscreen here https://prnt.sc/vnljtt

[Logit]

.
Up above add :

Dim sht as worksheet

[sorovince]

Now that error is gone. But now after I click on run I get this..

https://prnt.sc/vnloff

And after I click on debut I get this...

https://prnt.sc/vnlo5d

[Logit]

.
I almost NEVER do this ... but I am stumped !

I don't know the answer but I am certain someone else here does.

My apologies.