0

Good Afternoon, so for the past few weeks I've been trying to better understand the reasoning behind this run-time error whenever I try to remove unique (case sensitive) duplicates (ie. HOSTID and HostID should not be considered duplicates of each other.) This continue's to stem from my initial question here: Run Time Error when running a VBA string to remove unique duplicates

Shoutout to @BigBen for the assistance but unfortunately I'm still having some issues all depending on how I play around with the code.

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

grants me a mismatch error highlighting For i = 1 To UBound(x, 1)

If I try to be cheeky and use x = Range("AA1:AA100000" & lr).Value and Range("AA1:AA100000" & lr).ClearContents respectively, I don't get an error but half of my host id's get pushed all the way to the bottom of the excel document (and duplicates remain)

Please, any assistance would be appreciated. Youtubing this has been useless and I'm unable to find any article that seems relevant.

Gil Araujo
  • 3
  • 2
  • 2
    what is the value of `lr` when it errors? – Scott Craner Sep 03 '20 at 18:56
  • you'll have to forgive, but I'm not sure how I would get that information for you. I am rather novice in this matter. I found this site and the linked questions that match what I wanted to do. – Gil Araujo Sep 03 '20 at 19:10
  • When it errors you can click on debug. It will return to the VBE and in the Locals window you will see a row with `lr` and a value. – Scott Craner Sep 03 '20 at 19:12
  • so interestingly enough, when I was going to look at that information for you, I noticed that for some reason when executing this part of the macro '''Sheets("License Activity History").Select Range("LAH[HOST_ID]").Select Selection.Copy Sheets("Analysis").Select Columns("AA:AA").Select ActiveSheet.Paste Application.CutCopyMode = False''' It appears to have copied what should've been only 15 cells over and over again until it reached the bottom of the excel worksheet. Any thoughts? If it's easier I don't mind posting everything. Let me know what's best – Gil Araujo Sep 03 '20 at 19:41
  • If that is clarification to this question please [edit] the post to include the clarification. If it is a new question then create a new post. – Scott Craner Sep 03 '20 at 19:48
  • apologies for that, i tried but it didn't let me, I'll just delete the comment – Gil Araujo Sep 03 '20 at 20:00
  • please feel free to close this thread. Turns out I was able to resolve the issue and it had nothing to do with the coding above. thank you for the assistance – Gil Araujo Sep 04 '20 at 19:24

0 Answers0