VBA to advance to next unique criteria in Autofilter list

[KB_02]

I am trying to write a macro that will advance to the next unique criteria in an autofilter list.

I have roughly 2500 rows of data, but only need to filter by one criteria: The company name.

Using the Macro recorder to switch from Company A to Company B, I get this:

ActiveSheet.Range("$A$4:$A$2500").AutoFilter Field:=1, Criteria1:= _"Company Name B"

In the spread sheet I could have over 250 company names, but I sometimes have the need to go through them one by one to pull all data for each company. I currently just use the auto filter, but it takes some considerable time to pull down the list, deselect and then re-select the next name.

Is there a way to tell the criteria to just look for the next unique value in the list?

 

[Fluff]

What do you want to do once the autofilter changes?

 

[KB_02]

Well, ultimately, for each company I need to review the information and then print it out. The information I receive changes every month, so the workbook I am building will allow me to just drop the bulk info into one sheet and then through formulas and VBA go through the data one company at a time. getting the auto filter to advance is the one thing I can't seem to figure out.

Here is a example of what I have for bulk data:

Company Name      Order Number     Dataset 1    Dataset 2   order date
A Company            1000123456        ghjghjk       yuiyui         12/1/2018
A Company            1000123457        ghjghjk       yuiyui         12/2/2018
B Company            1000123458        ghjghjk       yuiyui         12/1/2018
B Company            1000123459        ghjghjk       yuiyui         12/5/2018
C Company            1000123460        ghjghjk       yuiyui         12/3/2018
C Company            1000123461        ghjghjk       yuiyui         12/5/2018
C Company            1000123462        ghjghjk       yuiyui         12/12/2018

I have the Autofilter set on the company name column. By running the VBA, I would like it to just advance to the next name on the list.

 

[Fluff]

How about

[COLOR=#ff0000]Option Explicit
Dim FltDic As Object[/COLOR]
Sub KB_02()
   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(xlUp))
         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 2 lines in red must go at the very top of the module, before any code.
It assumes that you have a header in row 4 with data starting in row 5

 

[KB_02]

I DO have a header in row 4 with data starting in row 5.

Thank you for the quick response. The code is not quite working, though. When I run the VBA, it will only go to the first company name in the list of names, regardless of which name it is currently on. If I am on Company G and run the VBA, it will go to Company A instead of moving on to Company H. And if it on A it will stay on A and not advance to B.

 

[Fluff]

The first time it's run it will show the first customer in col A.
The 2nd time it's run it should show the 2nd customer in col A and so on.
If you close & re-open the workbook, it will start at customer A again

 

[Akuini]

I DO have a header in row 4 with data starting in row 5.

Thank you for the quick response. The code is not quite working, though. When I run the VBA, it will only go to the first company name in the list of names, regardless of which name it is currently on. If I am on Company G and run the VBA, it will go to Company A instead of moving on to Company H. And if it on A it will stay on A and not advance to B.

Is your data always sorted by col A when you apply the autofilter?
If yes, then try this:

txt = Range("A" & Rows.count).End(xlUp).Offset(1)
If txt = "" Then txt = Range("A5")
ActiveSheet.Range("$A$4:$A$2500").AutoFilter Field:=1, Criteria1:=txt

 

[KB_02]

The first time it's run it will show the first customer in col A.
The 2nd time it's run it should show the 2nd customer in col A and so on.
If you close & re-open the workbook, it will start at customer A again

That's the thing, it's not progressing. I inserted and button and assigned the VBA to it, and I can sit there and click it repeatedly and it will still only show the first value, "Company A."
I copied your text verbatim. Could that be the problem? Should I have adjusted some part of the code to my specific worksheet?

Is your data always sorted by col A when you apply the autofilter?
If yes, then try this:

txt = Range("A" & Rows.count).End(xlUp).Offset(1)
If txt = "" Then txt = Range("A5")
ActiveSheet.Range("$A$4:$A$2500").AutoFilter Field:=1, Criteria1:=txt

I can't get that to work at all. Seems to hang up on the defining of "txt."

 

[KB_02]

I GOT IT!

I figured it out. I had to replace (xlUp) with (xlDown).

Thanks, again, for your help! It works just like I want it to now.

 

[Fluff]

Glad you got it working & thanks for the feedback