Solved Filter table based on input in two cells

October 17, 2015 at 06:19:38
Specs: Windows 7
I have data in range A3:P400. I want to input a value in cell A1 and the table to autofilter based on column A3:A400. I want to have one more condition that the same table after filtering as per input in cell A1 shall also filter column P3:P400 based on input in cell B1. Deleting data in B1 should reset filter in column P3:P400 and deleting data in A1 to remove filter in A3:A400

Please help me with a VBA code. I have no experience in writing VBA codes


See More: Filter table based on input in two cells

Report •

#1
October 19, 2015 at 06:38:33
Try this:

Right click the sheet tab for the sheet where you want the filtering done and chose View Code.

Paste the following into the window that opens, then enter your criteria in A1 and/or B1.

Private Sub Worksheet_Change(ByVal Target As Range)
'If change is made to A1 or B1, filter A3:P400 based on those 2 cells
    If Target.Address = "$A$1" Or Target.Address = "$B$1" Then
      Range("A3:P26").AutoFilter
        ActiveSheet.Range("$A$3:$P$400").AutoFilter Field:=1, Criteria1:=Range("A1")
        ActiveSheet.Range("$A$3:$P$400").AutoFilter Field:=16, Criteria1:=Range("B1")
    End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
October 21, 2015 at 03:39:30
Thanks a lot. Is there a way to protect the work sheet except cell A1 and B1 and still have this function of auto filter using VBA code ? I do not want accidental erase of contents in these cells which are filtered

Report •

#3
October 21, 2015 at 06:13:47
✔ Best Answer
The code below will Unprotect the worksheet so that the filter can be applied and then Protect the worksheet again.

The first thing you need to do is Unlock A1:B1 via the Format Cells...Protection tab so that users can enter data in those cells. Then you can Protect the worksheet, choosing whatever options that you require.

As written, the code uses zzzzz as the password. You can change that to whatever you want, or eliminate the Password:="zzzzz" from both instructions if you don't want to password protect the sheet. If the protection is just to prevent accidental changes to the data, as opposed to malicious changes, then using a password is up to you.

If you do want to use a password to protect the sheet, you should also consider using a password to protect the VBA code. If you don't, then users will be able to see (and change) not only the password, but the code itself. This link explains how to protect the VBA code so users can't even see it.

http://www.ozgrid.com/VBA/protect-v...

Here is the revised code, with the protection instructions included.

Private Sub Worksheet_Change(ByVal Target As Range)
'If change is made to A1 or B1, filter A3:P400 based on those 2 cells
    If Target.Address = "$A$1" Or Target.Address = "$B$1" Then
'Unprotect Sheet
     ActiveSheet.Unprotect Password:="zzzzz"
      Range("A3:P26").AutoFilter
        ActiveSheet.Range("$A$3:$P$400").AutoFilter Field:=1, Criteria1:=Range("A1")
        ActiveSheet.Range("$A$3:$P$400").AutoFilter Field:=16, Criteria1:=Range("B1")
'Protect Sheet
      ActiveSheet.Protect Password:="zzzzz"
    End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question