Using VBA to autofilter Multiple columns, with values from different sheet

vba excel excel-2013 autofilter
RobExcel picture RobExcel · Sep 18, 2017 · Viewed 23.4k times · Source

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.

Answer

Shai Rado picture Shai Rado · Sep 18, 2017

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

Related questions

Clear contents and formatting of an Excel cell with a single command

If you want to clear the contents of a cell or range in Microsoft Excel, you can use .ClearContents. If you also want to clear the formatting, you can use .ClearFormats. Sheets("Test").Range("A1:C3").ClearContents Sheets("Test").Range("…

Read More
Remove Duplicates from range of cells in excel vba

I'm trying to remove duplicates in excel 2013 VBA. but I'm getting error "object does not support this property or method". The problem is I don't have static range to select. I want remove duplicates from the column heaader 'abcd'. Cells.…

Read More
No Add-In Tab Though Add-In is Loaded and Working

I wrote an Excel VBA add-in over ten years ago, which uses the old Commandbars to put a menu in Excel's Tools menu (in Excel 2003 and earlier) or in Excel's Add-Ins tab (in Excel 2007 and later). I've never had a …

Read More