Macro to advance to next value in filtered data?

[KB_02]

Hello,
I have a spreadsheet with over 1600 rows of data. In Column A, there are around 250 or so unique values. I currently have column A filtered so that I can just look at the rows with the specific value I want to see in column A.

Is there a way to run a macro so that the filtered data would advance to the next unique value in the list?

Example:

Customer Name:   Data Set 1:    Data Set 2:   Data Set 3:
Customer A       This Thing     That Thing    Something Else 
Customer A       This Thing     That Thing    Something Else 
Customer A       This Thing     That Thing    Something Else 
Customer B       This Thing     That Thing    Something Else 
Customer B       This Thing     That Thing    Something Else 
Customer C       This Thing     That Thing    Something Else

If I have column A filtered to look at just Customer A, I want a macro that I could run just to change the filter to Customer B (and then C, then D, and so on...).

Doesn't seem like too big an issue, until you start getting down into the 125th customer name, do what you need to do and then change to customer #126 , you know?

Thanks!

 

[DanteAmor]

Clients are sorted in column A?

 

[Fluff]

Maybe
In the sheet module you want filtered

Private Sub Worksheet_Activate()
   Dim Cl As Range
   Dim i As Long

   Set FltrDic = CreateObject("scripting.dictionary")
   For Each Cl In Range("A2:A[COLOR=#ff0000]8000[/COLOR]")
      If Not FltrDic.Exists(Cl.Value) And Cl.Value <> "" Then
         FltrDic.Add Cl.Value, i
         i = i + 1
      End If
   Next Cl
End Sub

Change the value in red if needed to a row you wont exceed.
And then in a Regular module

[COLOR=#0000ff]Public FltrDic As Object[/COLOR]
Sub KB_02()
   Dim Vlu As String
   Vlu = Range("A2:A" & Rows.Count).SpecialCells(xlVisible)(1).Value
   Range("A1").AutoFilter 1, FltrDic.Keys()(FltrDic(Vlu) + 1)
End Sub

Where the line in blue must be at the very top of the module, before any code

 

[DanteAmor]

If the clients are ordered in column A, then execute the following macro, every time you execute the macro, it will filter the data with the following value.

Sub Macro3()
    wnext = Range("A" & Rows.Count).End(xlUp)(2).Value
    If wnext = "" Then wnext = Range("A2").Value
    ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=wnext
End Sub

 

[KB_02]

Thanks!
I was reminded that I had asked this question before and ended up with the following code which is pretty much what Fluff had suggested. I adapted it to my current project and it works pretty well (I've added a print command, as you can see):

Option Explicit
Dim FltDic As Object
Sub GO_THROUGH_CUSTOMERs()
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
    IgnorePrintAreas:=False
   Dim Cl As Range
   Static i As Long
   If FltDic Is Nothing Then
      Set FltDic = CreateObject("scripting.dictionary")
      For Each Cl In Range("A5", Range("A" & Rows.Count).End(xlDown))
         FltDic.Item(Cl.Value) = Empty
      Next Cl
   End If
   If i = FltDic.Count Then i = 0
   Range("A4:A7800").AutoFilter 1, FltDic.Keys()(i)
   i = i + 1
End Sub

The codes works for what I need, but it is not exactly perfect.
If I need to go back to a previous selection, when I run the code again, it will pick up where it left off rather than where I am. If I leave off at Customer G, change the selection back to Customer B, then run the code again, it will pick up with Customer H rather than advancing from where I am.
Why does it do that?
Some may not see that as a downfall but rather a benefit, but I would just like to know why it happens.

 

[Fluff]

It's because you have declared the variable i as Static. That means that it's remembered.
If you try either Dante's code or mine, you'll find they don't do that.

 

[KB_02]

Gotcha.
And of course its not as easy as just changing Static to Dim...

(For some reason, I can't seem to get your code to work.
With yours I get the error message: Object variable or With block variable not set.)

What I have works, but is static. What Dante gave me works and is more fluid. Fluff has given me another option and some good insight. Thank you, both. Looks like I am good to go.

 

[Fluff]

Glad it's sorted & thanks for the feedback

 

[DanteAmor]

A pleasure to help you. Thanks for the feedback.