tweak code help

[overbet]

I have this macro I use a lot and I need help changing it. The macro removes case sensitive duplicate values from column A, opposed to Excel's remove duplicates feature that ignores case sensitivity.

Can someone show me how to change this code so that instead of removing case sensitive duplicates from column A it instead removes the case sensitive duplicates from the cells I currently have selected? Also, important that the cells do not shift when the duplicate values are removed. If it makes the other data on the sheet shift it would cause the sheet to not function.

Thank you

Sub RemoveDupeCase()
    Dim a, e, x
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        a = .Value
        .ClearContents
        With CreateObject("Scripting.Dictionary")
            For Each e In a
                .Item(e) = Empty
            Next
            x = .keys
        End With
        .Resize(UBound(x) + 1).Value = Application.Transpose(x)
    End With
End Sub

 

[Peter_SSs]

Will your selection be a single rectangular range ( eg B4:Z10 or Column F or rows 3:5)?

Or could it be a multi area range made up of disjoint cells/ranges?

 

[overbet]

My selection is always row numbers. I select the row number on the left side and drag select the data. I might select rows 8 through 28 for example and it selects all the way across the entire sheet to the last column. 90% of the data in in column A and the data in any other columns would never be duplicate to anything in column A so that is the simplest way for me to select data. Sometimes I hold down ctrl and skip some rows and select rows below what I have drag selected.

 

[Peter_SSs]

My selection is always row numbers. I select the row number on the left side and drag select the data. I might select rows 8 through 28 for example and it selects all the way across the entire sheet to the last column. 90% of the data in in column A and the data in any other columns would never be duplicate to anything in column A so that is the simplest way for me to select data. Sometimes I hold down ctrl and skip some rows and select rows below what I have drag selected.

In that case, try this on a copy of your workbook.

Sub Remove_Dupe_Case()
  Dim d As Object
  Dim c As Range
  Dim s As String
  
  Application.ScreenUpdating = False
  Set d = CreateObject("Scripting.Dictionary")
  For Each c In Intersect(Selection, Columns("A"))
    s = c.Text
    If Len(s) > 0 Then
      If d(s) = 1 Then
        c.ClearContents
      Else
        d(s) = 1
      End If
    End If
  Next c
  Application.ScreenUpdating = True
End Sub

 

[overbet]

Wow works like a charm. Thank you so much. That is going to save me a lot of time. I have been copying and pasting the data into a blank sheet in column A then running the macro I had, then pasting the clean data back into the source. Thank you again. Cheers!

 

[Peter_SSs]

You are welcome - all good news.