0

So I initially asked how to remove unique duplicates based on case sensitivity (please refer to the link below: Excel: Removing Duplicates Based On Case Sensitivity

and ultimately I was guided to the following link:

How to remove duplicates that are case SENSITIVE in Excel (for 100k records or more)?

This time I'm using column q to test out the formula and so far the following formula works:

Sub duptest()

Sheets("Analysis").Select

Dim x, dict
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
x = Range("Q1:Q100000" & lr).Value
Set dict = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(x, 1)
    dict.Item(x(i, 1)) = ""
Next i
Range("Q1:Q100000" & lr).ClearContents
Range("Q1").Resize(dict.Count).Value = Application.Transpose(dict.keys)


End Sub

My issue, however, is that I would prefer that the range be the entire column Q but when making the following change:

Sub duptest()

Sheets("Analysis").Select

Dim x, dict
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
x = Range("Q:Q" & lr).Value
Set dict = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(x, 1)
    dict.Item(x(i, 1)) = ""
Next i
Range("Q:Q" & lr).ClearContents
Range("Q1").Resize(dict.Count).Value = Application.Transpose(dict.keys)


End Sub

I get an error stating "Run-time error '1004': Method 'Range' of object '_Global' failed

I thought I would be cute and make the ranges "Q1:Q1000000" but I end up with the same error. Any advise would be appreciated. I do not wish for this to reflect on the entire workbook nor the worksheet, I would like for this to occur only for column Q.

apologies for the novice questions.

Gil Araujo
  • 3
  • 2
  • `Range("Q1:Q" & lr).ClearContents`. You're missing a starting row `1` in the second code snippet. And you don't want to "hard-code" a last row, that's what `lr` is for. – BigBen Jul 30 '20 at 17:10
  • Same for `x = Range("Q1:Q" & lr).Value` – BigBen Jul 30 '20 at 17:11
  • Say `lr` equals `1000`. `Range("Q1:Q100000" & lr).ClearContents` doesn't work because that's equivalent to `Range("Q1:Q1000001000")`. – BigBen Jul 30 '20 at 17:13
  • ah ok I understand, thank you. So you recommend that I remove the lr portion aswell?, let me try that out and I'll get back to you on that – Gil Araujo Jul 30 '20 at 19:18
  • No, you should use the `lr`... it should always be `"Q1:Q" & lr`. – BigBen Jul 30 '20 at 19:19
  • understood, I kept that in there. After making the changes suggested I now get a "type mismatch" error for the following string: For i = 1 To UBound(x, 1) looks like further research is needed. I'm very new to this so perhaps understanding what I'm asking to happen here will help. if there's an easy solution to this I would appreciate it otherwise it looks like my main question was answered so thank you :-) – Gil Araujo Jul 30 '20 at 19:24

0 Answers0