Go To Next Filter

[cwojtak]

Hello, I have a table with three columns. Columns A, B and C.

Column A can be whatever I decide.
Column B can be three letters, A (actioned), D (need to look at still), or N (no action required).
Column C represents a products SKU.

First I filter column B to the letter 'D' as then I am only looking at the SKUs I need to action.

Next I filter column C to the first product SKU in the list.

Then I look at column A and decide if I need to make a change. Once I have made that decision I either press Ctrl+Shift+A to change the values in column B to 'A' for actioned, or I press Ctrl+Shift+N to change the values in column B to 'N' for not actioned. The macros which do that are listed below.

I would like to add a portion at the end of the code that automatically selects the next SKU in column C.

Attached is a workbook for your reference.

Sub Actioned()

' Keyboard Shortcut: Ctrl+Shift+A


Range("B1", Range("B1").End(xlDown)).FormulaR1C1 = "A"
'Here is where I'd like to move to the next SKU in the filter

End Sub


Sub NoActionRequired()


' Keyboard Shortcut: Ctrl+Shift+N


Range("B1", Range("B1").End(xlDown)).FormulaR1C1 = "N"
'Here is where I'd like to move to the next SKU in the filter

End Sub

*Macros inside example Wkbk below are not assigned to shortcut keys*
2591625916

[p45cal]

try these three:
Sub Actioned()
' Keyboard Shortcut: Ctrl+Shift+A
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set yy = Intersect(.Columns(2), .Cells, .Offset(1)).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not yy Is Nothing Then yy.Value = "A"
End With
'Here is where I'd like to move to the next SKU in the filter
blah
End Sub

Sub NoActionRequired()
' Keyboard Shortcut: Ctrl+Shift+N
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set yy = Intersect(.Columns(2), .Cells, .Offset(1)).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not yy Is Nothing Then yy.Value = "N"
End With
'Here is where I'd like to move to the next SKU in the filter
blah
End Sub

Sub blah()
Dim yy As Range
With ActiveSheet.AutoFilter.Range
.AutoFilter Field:=3
On Error Resume Next
Set yy = Intersect(.Columns(3), .Cells, .Offset(1)).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not yy Is Nothing Then .AutoFilter Field:=3, Criteria1:=yy.Cells(1).Value
End With
End Sub
Your attachment with only two columns bears little resemblance to your narrative.
Note that you must filter column B first, it will work with whatever you filter column B by.

[cwojtak]

Works perfect! Thanks a ton p45cal! The other solutions I was looking at online were much messier.

[p45cal]

Less mess:
Sub Actioned()
' Keyboard Shortcut: Ctrl+Shift+A
blah "A"
End Sub

Sub NoActionRequired()
' Keyboard Shortcut: Ctrl+Shift+N
blah "N"
End Sub

Sub blah(Letter)
Dim yy As Range
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set yy = Intersect(.Columns(2), .Cells, .Offset(1)).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not yy Is Nothing Then yy.Value = Letter
.AutoFilter Field:=3
On Error Resume Next
Set yy = Intersect(.Columns(3), .Cells, .Offset(1)).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not yy Is Nothing Then .AutoFilter Field:=3, Criteria1:=yy.Cells(1).Value
End With
End Sub

[cwojtak]

I'm struggling to implement this in the larger file I need it for. Here are the references for the larger file.

Column A = Columns AS:AY (Numerical reference = 45:51)
Column B = Column DN (Numerical reference = 118)
Column C = Column DQ (Numerical reference = 121)

The headers in which the table is filtered on begin in row 4. Below is my best attempt at getting it to work in the larger file. It changes the letters in Column DN as expected but rather than go to the next option in the filter it just unselects all options so the data is all hidden.


Sub Actioned()' Keyboard Shortcut: Ctrl+Shift+A
blah "A"
End Sub


Sub NoActionRequired()
' Keyboard Shortcut: Ctrl+Shift+N
blah "N"
End Sub


Sub blah(Letter)
Dim yy As Range
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set yy = Intersect(.Columns(118), .Cells, .Offset(3)).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not yy Is Nothing Then yy.Value = Letter
.AutoFilter Field:=3
On Error Resume Next
Set yy = Intersect(.Columns(118), .Cells, .Offset(3)).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not yy Is Nothing Then .AutoFilter Field:=121, Criteria1:=yy.Cells(4).Value
End With
End Sub

[p45cal]

What is the top left cell address of the autofiltered range (the leftmost cell of the cells with dropdown)?

[p45cal]

If it's as I suspect column A, then keep the .offset(1) instances and the .Cells(1) instance.
Change the line .AutoFilter Field:=3 to .AutoFilter Field:=121.

[cwojtak]

Top left cell of the table is 'A4'. I've changed it to the code listed below and now it again changes column 118 as expected but clears the filter in column 121



Sub Actioned() ' Keyboard Shortcut: Ctrl+Shift+Ablah "A"
End Sub




Sub NoActionRequired()
' Keyboard Shortcut: Ctrl+Shift+N
blah "N"
End Sub




Sub blah(Letter)
Dim yy As Range
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set yy = Intersect(.Columns(118), .Cells(1), .Offset(1)).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not yy Is Nothing Then yy.Value = Letter
.AutoFilter Field:=121
On Error Resume Next
Set yy = Intersect(.Columns(118), .Cells(1), .Offset(1)).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not yy Is Nothing Then .AutoFilter Field:=121, Criteria1:=yy.Cells(1).Value
End With
End Sub

[p45cal]

Yes, second instance of
Set yy = Intersect(.Columns(118), .Cells, .Offset(1)).SpecialCells(xlCellTypeVisible)
should be
Set yy = Intersect(.Columns(121), .Cells, .Offset(1)).SpecialCells(xlCellTypeVisible)

[cwojtak]

Worked great thank you! For my understanding, I've added some notes to the final bit of code. Can you confirm my interpretation is accurate?



Sub Actioned()' Keyboard Shortcut: Ctrl+Shift+A
blah "A"
End Sub




Sub NoActionRequired()
' Keyboard Shortcut: Ctrl+Shift+N
blah "N"
End Sub




Sub blah(Letter)
Dim yy As Range
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set yy = Intersect(.Columns(118), .Cells, .Offset(1)).SpecialCells(xlCellTypeVisible) 'selects visible cells below Range("DN4")
On Error GoTo 0
If Not yy Is Nothing Then yy.Value = Letter ' Sets visible cells below Range("DN4") = Either A or N
.AutoFilter Field:=121 'Clears filters from column "DQ"
On Error Resume Next
Set yy = Intersect(.Columns(121), .Cells, .Offset(1)).SpecialCells(xlCellTypeVisible) 'Sets yy = range below the visible cells
On Error GoTo 0
If Not yy Is Nothing Then .AutoFilter Field:=121, Criteria1:=yy.Cells(1).Value 'Filters Column DQ to value of the first cell below the previously visible cells
End With
End Sub

[p45cal]

Added lines with comments beginning with 'debug line:

Sub blah(Letter)
Dim yy As Range
Stop 'debug line: You can now step through the macro one line at a time with F8 on the keyboard, watching what happens on the sheet.
With ActiveSheet.AutoFilter.Range 'everything within With…End With that starts with a dot refers to this range.
'all offsets and field numbers are relative to this block of cells. If you INSERT extra BLANK columns to the left (not included in the Autofilter) the below code will still work.
.Select 'debug line: to show you that range; it includes the headers of the autofilter. Better viewed with no filters active.
Intersect(.Cells, .Offset(1)).Select 'debug line: only the databody of the filter, used below (.Cells is the whole autofilter range)
On Error Resume Next 'to avaiod the 'no cells found' error message if there are no visible cells.
Set yy = Intersect(.Columns(118), .Cells, .Offset(1)).SpecialCells(xlCellTypeVisible) 'selects visible cells below Range("DN4"). Doesn't select, but yes, yy refers to them.
On Error GoTo 0 'reset normal error reporting/handling.
If Not yy Is Nothing Then yy.Select 'debug line: so you can see what yy refers to.
If Not yy Is Nothing Then yy.Value = Letter ' Sets visible cells below Range("DN4") = Either A or N, but only if there are any.
.AutoFilter Field:=121 'Clears filters from column "DQ". Yes, column 121 of the Autofilter range, it may not always be column DQ. Also re-applies the filters of other columns.
On Error Resume Next
Set yy = Intersect(.Columns(121), .Cells, .Offset(1)).SpecialCells(xlCellTypeVisible) 'Sets yy = range below the visible cells
On Error GoTo 0
If Not yy Is Nothing Then yy.Select 'debug line: so you can see what yy refers to.
If Not yy Is Nothing Then yy.Cells(1).Select 'debug line: so you can see what yy.cells(1) refers to.
If Not yy Is Nothing Then .AutoFilter Field:=121, Criteria1:=yy.Cells(1).Value 'Filters Column DQ to value of the first cell below the previously visible cells
End With
End Sub

[cwojtak]

Incredible! What a well thought out bit of code. The additional notes you put in there have helped me learn tremendously. Do you always use intersect to define your range? If so why and if not so under what circumstances would you not use intersect to define your range? I'm assuming you typically use it as it makes offsetting much easier?

[p45cal]

Do you always use intersect to define your range?
Certainly not.

If so why and if not so under what circumstances would you not use intersect to define your range? I'm assuming you typically use it as it makes offsetting much easier?
I use it typically when I have a block of cells with headers and I want to operate on everything but the header row. That block could have come from a .CurrentRegion statement, or be a range exposed by Excel object model to VBA, such as in this case the Autofilter.Range range.
The Autofilter.Range is a block of cells that the Autofilter works on but includes the headers of the Autofilter. (By the way, the ListObject (VBA for an Excel Table proper) also has a .Range the same as the Autofilter, but it also has a .DataBodyRange range which is everything but the headers.)
So if you take that original block of cells, then take the .offset(1) (1 row down) of that range, then intersect them, you end up with the data body only.

If you have a range which has both headers at the top AND the left, you could equally obtain the data body with the likes of intersect(rng,rng.offset(1,1)).

This sort of thing will fail if the offset of a range tries to go beyond the edges of a worksheet where you could use the likes of rng.resize(rng.rows.count-1).offset(1) instead, and even with that you have to be careful you don't do rng.offset(1).resize(rng.rows.count-1) instead (the order of the operations matters).
(rng is a range in the snippets above.)

[cwojtak]

Thank you p45cal!