Macro that goes through a filtered list

[da_shee]

Hi

I'm trying to write a macro that goes through every value in a filtered list.

Specifically, I'd like the macro to select the first value in the filter. Perform the operation. Select the second value in the filter. Perform the operation. Select the third value in the filter. Perform the operation. Etc...

Does anyone know if this is possible?

Shee

 

[Domski]

Hi,

This will loop through the visible cells in column B as an example:

Sub test()
Dim lastRow As Long, myrange As Range
lastRow = Cells(Rows.Count, 2).End(xlUp).Row
For Each myrange In Range("B2:B" & lastRow).SpecialCells(xlCellTypeVisible)
MsgBox myrange
Next myrange
End Sub

Dom

 

[Jon von der Heyden]

Hi Shee

Welcome to the board!

I'm not entirely sure what you are asking, but one way I might interpret your questions is that you want to loop thru each value within the filter (drop-down). This macro here uses scripting dictionary to create a list of all unique items in your filtered range. Then it will loop thru each value and filter the range according to that value. As means of example I have then gone ahead and copied the table of filtered data to another sheet but you can adapt it to do something else.

Note, requires reference to Microsoft Scripting Runtime (in the VBE go to Tools > References and enable it). Pkease pay attention to the comments in the code as you will need to adapt it to your data structure.

Sub LoopFilter()
'// set reference to microsoft scripting runtime (tools > references)
    Dim rngCELL As Range
    Dim rngSEARCHAREA As Range
    Dim dic As Scripting.Dictionary
    Dim dicITEM As Variant
 
    '// change to fit your filter range (excluding column label)
    Set rngSEARCHAREA = ActiveSheet.Range("A2:A50")
 
    If rngSEARCHAREA Is Nothing Then GoTo Finish
 
    Set dic = New Scripting.Dictionary
    For Each rngCELL In rngSEARCHAREA
        If Not dic.Exists(rngCELL.Value) And Not rngCELL.Value = vbNullString Then
            dic.Add rngCELL.Value, rngCELL.Value
        End If
    Next
 
    If Not dic Is Nothing Then
        For Each dicITEM In dic.Items
            With rngSEARCHAREA.Offset(-1).Resize(rngSEARCHAREA.Rows.Count + 1)
                .AutoFilter field:=1, Criteria1:=dicITEM
                '// do whatever it is that you want to do _
                    in this example I'm copying each filtered table to sheet2 _
                    and to the next available blank row
                .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Copy
                    Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
                .AutoFilter field:=1
            End With
        Next dicITEM
    End If
Finish:
    Set rngSEARCHAREA = Nothing
    Set dic = Nothing
 
End Sub

 

[da_shee]

Thanks, that was helpful but I'm looking for a macro that goes through the actual filter as opposed to a filtered list.

The Macro I need would select Shop A, perform the operation. Then select Shop B, perform the opertation. Then select Shop C, perform the operation. etc...

Is it possible to go through a filter like this using a amcro?

Any help would be appreciated.

Shee

 

[Jon von der Heyden]

That's effectively what my code does.

 

[Jon von der Heyden]

What is the opertaion that you want to perform? And what column is your filter in?

 

[Domski]

A bit less technical than Jon's but another approach might be to use a collection to hold the unique values in the column. You can then loop through each item in the collection, apply the filter etc...

There an example here which could easily be adapted:

http://spreadsheetpage.com/index.php/tip/filling_a_listbox_with_unique_items/

Dom

 

[Jon von der Heyden]

Hi Dom

The dictionary object is apparently faster than using a collection. At least that is what Bob Bovey, Dennis Wallentin, Stepeh Bullen and John Green tell us in Pro Excel Development 2nd edition. Not sure if it really makes any difference in the grand scheme of things. Just thought I'd share it with you.

Also the dictionary supports the Exists property which is a real helper here when it comes down to collecting a list of unique values. Although the method you have suggested also has a useful way of excluding dupes.

I'm not suggesting that there is anything wrong with your suggestion, only explaining why I opted for the dictionary in the 1st place.

 

[da_shee]

THanks jon

I had actually written my reply before I saw your code. I will let you know how I get on. Thanks again.

 

[da_shee]

To answer your question.

I have a list of 130 shops listed alphabetically over 3400 cells in Column A.

I have products in Column B. The amount of products associated with each shop varies so each shop has a different number of rows. (Between ten and twenty).

I then have sales in column C.

What I'm trying to do is add a new row after the last product of each shop. Column A for this row would correspond to the shop name. Column B would be named total. Column C would be the sum of all sales of the products of this shop.

Hence my need for the macro.

Thanks