Macro to advance to next value in filtered data?

KB_02

New Member
Joined
Sep 7, 2018
Messages
26
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:
HTML:
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!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Maybe
In the sheet module you want filtered
Code:
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
Code:
[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
 
Upvote 0
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.

Code:
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
 
Upvote 0
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):

Code:
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Glad it's sorted & thanks for the feedback
 
Upvote 0
A pleasure to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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