Filtering data using a dictionary

[Demirion]

Hi, I would like to create a macro that will filter the data, but the problem is, I don't know exactly what the data will be in the file. For this purpose, I used a dictionary that goes through a given column and saves the keys that I want to filter later. Unfortunately, I don't know how to get data from this dictionary. Below, the loop that goes through the given column. I added Debug.Print to check if the dictionary is working properly.

For Each key In dictCity.Keys
Set oCity = dictCity(key)
With oCity
Debug.Print key
ws1.Range("A1").Autofilter Field:=1, Criteria1:=key, Operator:=xlFilterValues
End With
Next key

 

[kevin9999]

I'm not sure how you get your dictionary keys because you've only shown a snippet of your code, however, this is how I would normally do it (using the name you've used for your dictionary).

With ws1.Cells(1, 1).CurrentRegion
    .AutoFilter 1, Array(dictCity.keys), 7
End With

 

[Demirion]

Yes this is a code snippet. The dictionary works fine because single keys are displayed. It partially works because it goes over all the keys, but I still don't know how to copy the scope for one key.

 

[kevin9999]

I don't follow what you mean by this:

how to copy the scope for one key.

Could you describe a before and after scenario?

 

[Demirion]

Suppose I have three key cities in my dictionary: London, Paris, Madrid. I want to copy a data range that will filter only for London, then only for Paris and finally for Madrid. I want to paste the copied data into my e-mail. I have to do it through the dictionary because I don't know what cities will be in the file.

 

[kevin9999]

Something along these lines (you'll have to adapt it to suit your actual data - and insert your copy-to location)

Option Explicit
Sub Loop_Through_Keys()
    Dim dictCity As Object, key As Variant, x, i As Long, ws As Worksheet
    Set ws = Sheet1
    Set dictCity = CreateObject("scripting.dictionary")
    x = Application.Transpose(Range("A2", Cells(Rows.Count, "A").End(xlUp)))
    
    For i = 1 To UBound(x, 1)   '<< fill the dictionary with unique values
        dictCity(x(i)) = 1
    Next

    For Each key In dictCity.keys   '<< begin looping through the keys
        With ws.Cells(1, 1).CurrentRegion
            .AutoFilter 1, key, 7
            .Offset(1).Copy         '<< at this point put your copy-to location
        End With
    Next key
    
End Sub

 

[Demirion]

It works the same way as my first code.

For Each key In dictCity.Keys
Set oCity = dictCity(key)
With oCity
Debug.Print key
ws1.Range("A1").Autofilter Field:=1, Criteria1:=key, Operator:=xlFilterValues
End With
Next key

It deletes some cities when copying, and I don't know what it depends on.

The line of code you entered works in a different way, but I don't know if it's correct.

        With ws1.Cells(1, 1).CurrentRegion
.AutoFilter 1, Array(dictCity.keys), 7
End With

It copies all the cities, but one by one as they are on the sheet. I don't think that's what I mean. Now I am testing pasting on the sheet, but ultimately I want to copy these individual ranges to the content of individual e-mails. It is even possible?

 

[kevin9999]

It copies all the cities, but one by one as they are on the sheet.

My understanding is that this is what you were looking for.

I don't think that's what I mean.

I'll step back now until you have sorted out your actual understanding of what it is that you want.
Good luck and best wishes.

 

[Demirion]

I'm just testing pasting data on a blank sheet and it looks like this. The keys are not ordered in any way, it looks like I just copied all the data and pasted it into a blank sheet. I don't know if this will work for what I want to do. These were just tests. Ultimately, I want to paste the filtered data into Outlook for each key separately.

It gets complicated. That's why I'm leaving the dictionary for now, I'll come back to it later. For now, I will filter this data as standard. The next step is that I want to paste this filtered data into Outlook as a table. Could you give me some tips on how to do this?

 

[kevin9999]

Here's some links that may help

Mail Range/Selection in the body of the mail

How to paste multiple range of cells in email

Paste Excel range into outlook email body VBA