Filter loop through macro

[tommyleinen]

I have various workbooks that I need to check the data in by filtering on various columns to find those that need to be checked and then I filter through the resultant list using usually column 'C', checking line by line and marking those 'done'.

What I'd like to achieve is a button that filters to the next one on the list each time it is pressed, once I've marked them as 'done'.

There are many sheets I would like to use this on, so I intend to put this is in my personal macros for use on all sheets of similar makeup. However the position of the header row sometimes varies, from row 5 to row 9, and the headings filters I filter on are usually in columns C, E and column AT, however these too can vary. So ideally, what I'd need is to be able to do is influence the script by changing these parameters if necessary at the beginning of the process (perhaps a different macro that works in tandem).

There are autofilters across all headings on row 8 say, and usually I will filter 2 columns on say E and AT. The filter on E will be set to view all except 'x' (which are the ones I have marked done with an 'x') and AT is set to view all except '-'.

Those two columns have now provided those that I need to check, though sometimes I need to view all on those two and filter from a column from anywhere in between E and AT on the nonblanks - but that is a side issue.

I think the macro needs to first set the two filters and then copy the resultant 'list' of unique values from column C to perhaps a hidden sheet or another sheet, sort the list alphabetically and delete the top value / row each time the button is pressed and it cycles to filter in column C on the main sheet to the next value on the list. It would be ideal if the last value checked is retained on the main sheet above the 'done' column in E6, say.

I can provide a sample sheet using non identifiable data if necessary, any help with this would be much appreciated as it is well out of my league!!

 

[Yongle]

Let's try to get the basics of what you want working in a normal workbook and then get it working via personal workbook when all required features are in place
Let's build the code step by step and then you will understand it better if you need to modify later.
The code assumes that data filter is already in place (which it needs to be so that the data range can be established automatically, given 2 different header row possibilities)

First test code below to see if this correctly filters based on the 2 columns
- with both row5 and row9 header sheets

Sub Filter_E_and_AT()
    Dim Filtered As Range, AT As Range, ws As Worksheet
    Set ws = ActiveSheet
    Set Filtered = ws.AutoFilter.Range
    On Error Resume Next
    ws.ShowAllData
    On Error GoTo 0
    Filtered.AutoFilter Field:=5, Criteria1:="<>x", Operator:=xlAnd
    Filtered.AutoFilter Field:=46, Criteria1:="<>-", Operator:=xlAnd
End Sub

Is it ALWAYS columns E and AT or could those columns vary ?

 

[tommyleinen]

Hi Yongle,

Thanks for looking at this, yes that code has tested working through rows 5 to 9!

The data filter is always in place but it isn't always C, E and AT, they can vary (but not by much). I was thinking I could specify somewhere out of sight the columns to reference.
Eg, in BA1, BB1, BC1, I could specify the values 3,5 and 46 respectively. It's not as clean but I think it would be easier than having to set parameters each time as these 'should' still be there the next time I come to check the data!

You maybe had your own thoughts on how to tackle that problem.

 

[Yongle]

The data filter is always in place but it isn't always C, E and AT, they can vary (but not by much). I was thinking I could specify somewhere out of sight the columns to reference.
Eg, in BA1, BB1, BC1, I could specify the values 3,5 and 46 respectively. It's not as clean but I think it would be easier than having to set parameters each time as these 'should' still be there the next time I come to check the data!
You maybe had your own thoughts on how to tackle that problem.

As you have already indicated, sheet-specific instructions needs storing somewhere
Could be done in several different ways
- in the individual sheet (your suggestion)
- in a named ranges
- in the VBA (probably too cumbersome)
- in a single lookup table
etc

We need to determine what will work best for you in the longer term

Does any workbook contain more than one sheet that requires specified filtering ?
- if so, would the default filtering be identical for every sheet within the same workbook ?

It sounds as though the "x" and "-" filters should always be applied
Please explain the 3rd Filter ( set to filter on the nonblanks )
- is this always the SAME column within a given sheet?
- do you need an option for this filter to be switched between on/off ?

 

[tommyleinen]

As you have already indicated, sheet-specific instructions needs storing somewhere
Could be done in several different ways
- in the individual sheet (your suggestion)
- in a named ranges
- in the VBA (probably too cumbersome)
- in a single lookup table
etc

We need to determine what will work best for you in the longer term

Does any workbook contain more than one sheet that requires specified filtering ?
- if so, would the default filtering be identical for every sheet within the same workbook ?

It sounds as though the "x" and "-" filters should always be applied
Please explain the 3rd Filter ( set to filter on the nonblanks )
- is this always the SAME column within a given sheet?
- do you need an option for this filter to be switched between on/off ?

Hi Yongle,

Okay, go with my suggestion, in the individual sheet in specific cells BA1, BB1 & BB3. That would be best long term as the code would just need to be tweaked should the columns become standardised in future.

Thanks again.

 

[Yongle]

It sounds as though the "x" and "-" filters should always be applied
Please explain the 3rd Filter ( set to filter on the nonblanks )

- do you need an option for this filter to be switched between on/off ?

???

 

[tommyleinen]

Sorry, so the 'x' and the '-' will be on when cycling through the 3rd filter which is the one on in column 'C', the one for which I need to cycle through the next value at each button press.

Occasionally, I may need to stop what I'm doing with these 3 filters and manually filter a different column. So the aim is for the code to be able to 'remember' the last one it had checked, so it can pick up from where it stopped previously. There are filters on every column heading, right through from A to just before AT (the filter specified in BC1) - I think I said BB3 in error earlier! These need to remain in place. I need to be able to manually use these after using a macro I have to clear all filters. This may affect the new macro.

It would be ideal if each time your macro filters to a value in C, it populates that value in the cell two cells above the 2nd filter (specified location in BB1). That way I can see where I last got to when coming back. It could also be a reference for the macro to check when the button is pressed so that it can resume from a specified value rather than starting at the beginning again. Ideally it will cycle through the list alphapetically.

The workbook does contain other sheets that have filters on them, one of which I use to try to 'match' the value. I wasn't going to include this as a lot of the time some of the words do not match exactly what are in the main sheet. Now you mention it though, if it can be incorporated, it would be useful to filter on the same value on this other sheet (call it Sheet2 for convenience), if it is there. The filter on that sheet is a similar fashion, but only needs to be filtered on one column, usually col B. This could also be specified on the main sheet in BD1. If a match is found, it would be useful for it to populate the cell 3 above the 2nd filter in the main sheet as 'Matched', or if not found, then 'No match' .

The rest of the sheets with filters should be left unaffected by the macro.

I'm not sure about the macro being toggled on or off as I don't know what that would entail, but maybe my explanation above provides the answer you need.

Thank you again

 

[Yongle]

Occasionally, I may need to stop what I'm doing with these 3 filters and manually filter a different column. So the aim is for the code to be able to 'remember' the last one it had checked, so it can pick up from where it stopped previously.

Am I missing something?
"x" (= done) is one of the requested macro's filters (rows containing "x" are hidden)
Why does anything need to be remembered ?
Simply running the macro again filters out everything that is "done" and the first visible row is the next row for you to check

The above assumes that you are working down through every row and dealing with each row in turn and marking it off with an "x" before moving to the next row

 

[tommyleinen]

I need to check each line in groups of those of the same value, so then need to use the filter in C to go through the list by value.
This is step by step the process I follow:

The 'x' helps weed out those that have been done, leaving only those remaining to be looked at. I then use the filter in column C to manually untick all, then tick the first one on the list (they appear in alphabetical order on the filter drop down menu). This is a repetitive process of selecting the filter in C, and ticking the next one at the top of the list. Each time, I check the data, mark those lines with an 'x' and then go back to the filter in C and select the next one to look at. What I need the macro to do is to reduce it to a single button press to filter the next item on the filter list in 'C' after I have marked the last one with an 'x' in E. I have a macro button that I press to apply the 'x's. So, leaving the other filters in place, the macro needs to tick the next item in Col C. One useful thing about the way I work currently is that by placing an x in the 'done' column, when I go to select the next one on the list, the last ones done have vanished due to the 'x'. This is ideal for me but I don't know if that would happen using a macro.

 

[Yongle]

One useful thing about the way I work currently is that by placing an x in the 'done' column, when I go to select the next one on the list, the last ones done have vanished due to the 'x'

Isn't that is what I said in my post ?

Will post some code tomorrow