2

I want to use VBA to filter a dump-sheet by 2 columns, with criteria gotten from values on a different sheet in the same workbook.

Used code is:

Sub FilterOnCellValue()

With Sheets("Dump")
    .Range("A1:Z10000").AutoFilter Field:=9, Criteria1:=Sheets("ControlPlanning").Range("C1").Value, Field:=23, Criteria1:=Sheets("ControlPlanning").Range("C4").Value
End With

End Sub

For some reason this code filters only one column, while it should be filtering Columns with Number 9 and 23 on 2 different values. As i want to learn from this, explain my thinking error in this piece of VBA. Excel version is 2013, if this makes any difference.

Community
  • 1
  • 1
RobExcel
  • 171
  • 2
  • 6
  • 16
  • 1
    Possible duplicate of [Multiple Filtering in Excel VBA?](https://stackoverflow.com/questions/12040906/multiple-filtering-in-excel-vba) – Pᴇʜ Sep 18 '17 at 12:27
  • According to [Range.AutoFilter MSDN](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-autofilter-method-excel?f=255&MSPPError=-2147217396) you cannot twice Field parametter.. – Siyon DP Sep 18 '17 at 12:32
  • @Peh, no criteria from value there. – RobExcel Sep 18 '17 at 12:34
  • @TSion.D.P, works like a dime, whatever MSDN is saying. – RobExcel Sep 18 '17 at 12:34
  • @RobExcel It doesn't matter if you use a criteria from a cell value or a fixed string. The syntax around is the same, as you can see in the answer below. – Pᴇʜ Sep 18 '17 at 12:42
  • @Peh Fair enough. Got the answer I needed, so, what's the deal? – RobExcel Sep 18 '17 at 12:48
  • @RobExcel The deal is you stated in your question you "*want to learn from this*". So my aim was showing you that there is already a similar (almost same) question which already has the answer you were looking for (with only little knowledge transfer). So learning (how) to use search and find similar/same questions already asked and transfer that knowledge into your use case would be a much grater benefit for you then getting the solution on the silver plate. That was the deal. – Pᴇʜ Sep 18 '17 at 13:01

2 Answers2

10

Try to seperate the syntax to 2 lines:

Sub FilterOnCellValue()

With Sheets("Dump").Range("A1:Z10000")
    .AutoFilter Field:=9, Criteria1:=Sheets("ControlPlanning").Range("C1").Value
    .AutoFilter Field:=23, Criteria1:=Sheets("ControlPlanning").Range("C4").Value
End With

End Sub
Shai Rado
  • 33,032
  • 6
  • 29
  • 51
0

Use Advanced Filter to use "AND" & "OR" criteria on multiple columns.

If for example you need to filter "columnName1" > 10 or "columnName2" < 10 AND "columnName3" = "Yes"

  1. I would create another worksheet which will contain filter criteria

  2. in the filter sheet enter all the columnnames in one row.

  3. Example: If row 1 has headers then row 2 should contain the values for "AND" condition and row 3 should contain values for "OR" condition.

  4. for "columname1" value on row 2 should be ">10" and "columname2" value on row 3 should be "<10" and "columname3" value on row 2 should be "Yes"

  5. Use Advanced filter like below code in your macro, Change the filter sheet name and range as required.

    'Select Sheet and cells which needs to be filtered Range(Cells(2, 1), ActiveCell.SpecialCells(xlLastCell)).Select

    With Selection .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Sheets("filter").Range("A1:D3"), Unique:=False

    End With

Vivek Amirthakadeswaran
  • 66
  • 5