Able to uncheck item in column B's filter based on value found in column C with Excel VBA?

[gertie193]

I'm using Excel 2013 and I have a spreadsheet where the Filter setting is on. I am working on code that will uncheck an item in column B's filter if a certain value like, "Cat", is in column C. In the case below, it should just leave the Tree and Bark rows.

Fruit	Filter	Animal
Apples	14756	Cat
Oranges	20000	Cat
Tree	14756	Dog
Bark	78944	Bird

<tbody>
</tbody>

QUESTION:
I just want to see if someone can tell me if Excel is able to do this using Excel VBA? And if someone can start me on the path on how to go about it.

I don't see anything on the internet about this. I may be phrasing my search incorrectly in Google, but I've tried different ways today.

I'VE TRIED:
1. I have a For/Next loop, etc., with If/Then inside that but the line of code I'm concerned about to filter is:

ActiveSheet.Range(Cells(iBilling, "B"), Cells(iBilling, lastcolBilling)).AutoFilter Field:=2, Criteria1:=matchedaddress, Operator:=xlAnd

where 'matchedaddress' is the variable where I've stored "Cat".
Problem: But this will uncheck everything in column B I think b/c it's not offsetting to filter column B based on column C.

2. So I tried using Offset within the AutoFilter parameters. I get an error but I need it to do this.
Problem: But I get 'Run Time Error 450: Wrong number of arguments or invalid property assignment', which is probably b/c of the Offset within the Field parameter.

ActiveSheet.Cells(iBilling, "C").AutoFilter Field:=ActiveSheet.Range.Offset(0, -1), Criteria1:=matchedaddress, Operator:=xlAnd

Thank you for your help.

 

[AlphaFrog]

Can you post you whole code where you set the autofilter for column B ?

 

[gertie193]

Thank you, Alpha Frog. The code is below. It is a little more than what I originally described b/c I was trying to keep it simple.

It is trying to match values between Column D (not Column C as above) of the Billing workbook and Column K of the Address List workbook (so two workbooks). If there is a match, then activate the Billing workbook and uncheck that corresponding item in Column B's filter. So, in the table above, if 'Cat' is found in the other workbook, the Address List workbook, then uncheck '14756' and '20000' from column B b/c it is going to loop and check each cell to see if a match.

And to **CORRECT** what I said in my original post: it should only leave the 'Bark' row b/c if Cat is found then 14756 will be unchecked which will eliminate 'Apples' and 'Tree' row, and 20000 will be unchecked which will eliminate 'Oranges' row.

I just want to see if it can be done b/c there is nothing like it on Google that I could find, and see if someone can correct what I have below or get me started in the right direction.

The code is in a module in the PERSONAL workbook.

Sub FilterBillingBridges()

Dim wkbkBilling As Workbook
Dim iBilling As Long
Dim lastrowBilling As Long
Dim lastcolBilling As Long
Dim wkbkAddrList As Workbook
Dim jAddrList As Long
Dim lastrowAddrList As Long
Dim lastcolAddrList As Long
Dim matchedaddress As Variant


'BILLING WORKBOOK
Set wkbkBilling = ActiveWorkbook
lastrowBilling = ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row
lastcolBilling = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column


For iBilling = 2 To lastrowBilling
matchedaddress = wkbkBilling.ActiveSheet.Cells(iBilling, "D").Value

'ADDR LIST WORKBOOK (ADDRESS LIST)
Set wkbkAddrList = Workbooks(2)
wkbkAddrList.Activate
lastrowAddrList = ActiveSheet.Range("H" & Rows.Count).End(xlUp).Row
lastcolAddrList = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column


For jAddrList = 2 To lastrowAddrList
    If ActiveSheet.Cells(jAddrList, "K").Value = matchedaddress Then
       'ACTIVATE BILLING WKBK
       wkbkBilling.Sheets(1).Activate
       'WILL ADD CODE TO CHECK IF ALREADY UNCHECKED POSSIBLY??
       ActiveSheet.Range(Cells(iBilling, "B"), Cells(iBilling, lastcolBilling)).AutoFilter Field:=2, Criteria1:=matchedaddress, Operator:=xlAnd
       'THE FOLLOWING GIVES RUN TIME ERROR 450: Active Sheet.Cells(iBilling, "D").AutoFilter Field:=ActiveSheet.Range.Offset(0,-1), Criteria1:=matchedaddress, Operator:=xlAnd
     End If
Next jAddrList


Next iBilling


wkbkBilling.Sheets(1).Activate
wkbkBilling.ActiveSheet.Range("A1").Select


End Sub

 

[AlphaFrog]

I follow your description. One question; what method is used to select the column B filter criteria? If you want to deselect a filtered item in column B, it may matter how the items were originally selected. If you used code, can you post that as well?

 

[gertie193]

Thank you, Alpha Frog.

I'm not sure if you are meaning the method I use in VBA or how I filter on the spreadsheet itself, so I'm going to answer both.

How I select the column B filter criteria manually: (I know you probably mean VBA, but just in case.)
Below is how the spreadsheet looks where the filters are turned on but nothing is filtered. When I do this manually I have to open the filter drop down for column B in the little example below and unselect just the items under that column that match the addresses in column D (the animal column).

https://drive.google.com/open?id=0B9IyKJSJ52ghUHpzOG5fX0xJWkE

How I select the column B filter criteria VBA:

It's in the code in the original post, but let me show you something. When I record a macro of me unselecting the item I get:

ActiveSheet.Range("$A$1:$D$5").AutoFilter Field:=2, Criteria1:="=20000", Operator:=xlOr, Criteria2:="=78944"

Note: I just noticed the macro selects the whole data range. I don't in my code, but that is fixable.

It basically uses the items I have left, not what I unselected, as the Criteria1 and Criteria2 in AutoFilter code.

I, however, tried to use the code below to put my 'matchedaddress' variable in the Criteria as below (which is I have in my original post):

ActiveSheet.Range(Cells(iBilling, "B"), Cells(iBilling, lastcolBilling)).AutoFilter Field:=2, Criteria1:=matchedaddress, Operator:=xlAnd

But as I'm writing to you, I realize I have two problems:

1) If Excel autoadds Criteria of what is LEFT and not what I want selected, then how can I use my variable as criteria, where my variable is what I want selected?
2) Can Excel filter one column if a value is matched in another column (the match that is found between two workbooks)?

Like I may have mentioned, this code works if you replace the AutoFilter code with, say, if a match between two workbooks is found then please highlight the row of the match. So, the problem with the code is just the 2 issues listed above, I think.

Thank you so much for your help, Alpha Frog!

 

[AlphaFrog]

You answered my question. I was asking how you initially select the column B filter items. You do it manually which is what I needed to know.

It basically uses the items I have left, not what I unselected, as the Criteria1 and Criteria2 in AutoFilter code.

That is the crux of the problem. There is no Remove Item vba method for Autofilter as far as I know.

The code below loops through all the filtered items in column B and adds them to a dictionary array. It then loops through all the items in column D and tests if they match column K from the other workbook. If matched, it removes the column B item from the dictionary array. The code then uses the remaining items in the dictionary as the criteria in a new autofilter for column B.

[color=darkblue]Sub[/color] FilterBillingBridges()
    
    [color=darkblue]Dim[/color] cell [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] arrAddrList [color=darkblue]As[/color] [color=darkblue]Variant[/color]
        
    [color=green]'ADDR LIST WORKBOOK (ADDRESS LIST)[/color]
    [color=darkblue]With[/color] Workbooks(2).Sheets(1)
        arrAddrList = .Range("K2", .Range("K" & Rows.Count).End(xlUp)).Value
    End [color=darkblue]With[/color]
    
    [color=darkblue]With[/color] CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        
        [color=green]'Add column B filtered items to dictionary array[/color]
        [color=darkblue]For[/color] [color=darkblue]Each[/color] cell [color=darkblue]In[/color] Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
            .Item(cell.Value) = 1
        [color=darkblue]Next[/color] cell
        
        [color=green]'Remove items from array if column D matched column K[/color]
        [color=darkblue]For[/color] [color=darkblue]Each[/color] cell [color=darkblue]In[/color] Range("D2", Range("D" & Rows.Count).[color=darkblue]End[/color](xlUp)).SpecialCells(xlCellTypeVisible)
            [color=darkblue]If[/color] IsNumeric(Application.Match(cell.Value, arrAddrList, 0)) [color=darkblue]Then[/color]
                [color=darkblue]If[/color] .Exists(cell.Offset(, -2).Value) [color=darkblue]Then[/color] .Remove cell.Offset(, -2).Value
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]Next[/color] cell
        
        [color=green]'Filter column B on remaining array items[/color]
        ActiveSheet.AutoFilter.Range.AutoFilter Field:=2, Criteria1:=.Keys, Operator:=xlFilterValues
        
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
End [color=darkblue]Sub[/color]

 

[gertie193]

Hello Alpha Frog, I see what you are asking now. I actually want a code that will not only lookup and match the values between the two workbooks (column D in the Billing spreadsheet and column K in the Address List spreadsheet--I have that code) but also, with VBA, unselect the matched items in column B of the Billing spreadsheet for me. I wanted to avoid unselecting the items manually b/c it is tedious when having to deal with hundreds of addresses sometimes more than once a day.

I'm going to look over this code--thank you for providing this--and see if I can modify it in some way for my needs. I would like to ask questions if I run into trouble. Like I mentioned, there is no mention of a code doing this on the internet, so I need some help.

EDIT: After reading your post again, I may have misunderstood you that the list in column B is already one I've filtered manually. Like I mentioned, I will look at this closely.

Thank you, Alpha Frog.

 

[gertie193]

Thank you, Alpha Frog. I have run it 8 different times and it works every time. Thank you so much for your help.

I have a couple of questions, please:

1) I misunderstood what you were saying when you said the code loops through filtered column B. You just mean that it loops through column B, which has the filter on--not that it is filtered in any way--and then adds the values in that column to the dictionary array, correct?

2) .Keys - I don't see much about that on the internet. I saw it used in code where someone did a sort using multiple criteria. How does Excel know what .Keys is?

For anyone interested:

1) To prep before using my version of Alpha Frog's macro, I have to make sure I name the billing workbook with the word 'Billing' in it with a capital 'B'. There are ways on how to make it case insensitive on the internet, but I left it.

2) I also need to manually look at the data to make sure an address isn't formatted differently in one workbook than another so the MATCH function in the code will not miss any matching addresses.

Below is the code I used with some modifications, one of them being that I had to get rid of referencing the 2 workbooks as Workbooks(1) and Workbooks(2). The code would look to the wrong workbook sometimes.

Sub FilterBillingBridges()
    
    Dim cell As Range
    Dim arrAddrList As Variant
    Dim strBillingWkbk As String
    Dim wkbk As Workbook
    
         
    'ADDR LIST WORKBOOK (ADDRESS LIST)
    With ActiveWorkbook.Sheets(1)
        'msgbox to make sure the arrAddrList is being populated using Address List workbook and not Billing workbook
        MsgBox "Address List Used Rows: " & .Range("K" & Rows.Count).End(xlUp).Row - 1 '-1 b/c minus the header
        arrAddrList = .Range("K2", .Range("K" & Rows.Count).End(xlUp)).Value
    End With
    
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
         
        For Each wkbk In Workbooks
            strBillingWkbk = wkbk.Name
            If InStr(strBillingWkbk, "Billing") Then
                'Activate workbook with the word 'Billing' in it
                Workbooks(strBillingWkbk).Activate
            End If
        Next
        'Add column B items to dictionary array
        For Each cell In ActiveSheet.Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
            .Item(cell.Value) = 1
        Next cell
        
        'Remove items from array if column D matched column K
        For Each cell In Range("D2", Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
            If IsNumeric(Application.Match(cell.Value, arrAddrList, 0)) Then
                If .Exists(cell.Offset(, -2).Value) Then .Remove cell.Offset(, -2).Value
            End If
        Next cell
        
        'Filter column B in Billing workbook on remaining array items that will manually be _
        deleted leaving the addresses that need to go to billing.
        ActiveSheet.AutoFilter.Range.AutoFilter Field:=2, Criteria1:=.Keys, Operator:=xlFilterValues
        
    End With
    
End Sub

 

[AlphaFrog]

1.) It loops through all the visible items in column B, whether it's filtered or not, and adds them to the Dictionary array. So if you manually set the filter criteria in column B before running this code, those previously filtered items are excluded.

2.) The .Keys is a property of the Dictionary object. In this code, .Keys is the remaining array of column B elements that were added and removed from the Dictionary. The VBA code that begins with a period refers back to the object in the With statement (.Item, .Exists, .Remove, .Keys are all properties and methods of Scripting.Dictionary). If you search for something like VBA Scripting Dictionary, you should find a lot of help and tutorials.

 

[gertie193]

Thank you very much, AlphaFrog. I really appreciate your help.