Removing items from dictionary based on range not working

SkywardPalm

Board Regular
Joined
Oct 23, 2021
Messages
61
Office Version
  1. 365
Platform
  1. Windows
I am attempting to remove items that are in a specific range from a dictionary. It works for the most part, but I am having instances where it is not removing an item even the the names match...

VBA Code:
    'for each cell in range of summary table, if in dict of tab names, remove
    For Each c In wsSUMMARY.Range("B17:B226")
        key = c.Value
        For Each k In sectDict.Keys
            'Debug.Print k, sectDict(k)
            If sectDict.Exists(key) Then
                sectDict.Remove key
                Debug.Print "Key #" & k & " " & sectDict(k) & " removed from sections dictionary"
            Else
                'code to show errors for wrong or not present key
            End If
        Next k
    Next c

Maybe there is something I'm overlooking?
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Hi @SkywardPalm
Thanks for posting on MrExce.


It seems to me that you only need this:

VBA Code:
  For Each c In wsSUMMARY.Range("B17:B226")
    If sectDict.exists(c.Value) Then
      sectDict.Remove c.Value
    End If
  Next


--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​
 
Upvote 0
Solution
Hi @SkywardPalm
Thanks for posting on MrExce.


It seems to me that you only need this:

VBA Code:
  For Each c In wsSUMMARY.Range("B17:B226")
    If sectDict.exists(c.Value) Then
      sectDict.Remove c.Value
    End If
  Next


--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​
Thanks for the quick response! Will this also remove the duplicates if there are multiple identical items in the dictionary?
 
Upvote 0
Thanks for the quick response! Will this also remove the duplicates if there are multiple identical items in the dictionary?
There are no duplicate keys in a dictionary.
When you add keys to a dictionary you can only add unique keys.
Or maybe you haven't understood the purpose of the dictionary. You can only store unique keys in the dictionary.

You can explain what you need, what is your ultimate goal.

Explain in detail and with examples:
1. What you have on the sheet
2. What you want to load in the dictionary
3. What you want to delete and
4 What is the final result you want to have.

If you provide us with complete information, it will be easier to provide you with an answer that helps you.
 
Upvote 0
There are no duplicate keys in a dictionary.
When you add keys to a dictionary you can only add unique keys.
Or maybe you haven't understood the purpose of the dictionary. You can only store unique keys in the dictionary.

You can explain what you need, what is your ultimate goal.

Explain in detail and with examples:
1. What you have on the sheet
2. What you want to load in the dictionary
3. What you want to delete and
4 What is the final result you want to have.

If you provide us with complete information, it will be easier to provide you with an answer that helps you.
Thanks for the insight. You're correct, I had it importing tab names from multiple sheets that had the same tabs and assumed it was pulling them all in and removing them as it iterated. After printing the dictionary I saw there were no duplicates and your code worked perfectly for me. Thank you for the help!
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top