I have a table Named "Combined" which is stored on one sheet of a work book.
On a second sheet I have the following Cell Range (in C1:F2)
Delivery | Column Ref | Column Ref | Available
Delivery ID | I | J | YES
I want to be able to use VBA to filter the table based on the values in this cell range
The Data drop column is a cell with a drop down list which uses VLOOKUP to populate the two column ref cells. These are the two columns that need to be filtered.
Column I needs to show all rows that <>"X" while column J needs to show all rows that equal the value in the available column.
I then need to be able to copy columns A,G and the column that appears in the first reference cell to cell A5 in the second sheet.
Is it possible to do this using VBA? I have been attempting to do this using IF statements, but it is very messy.
I have a piece of code I am attempting to modify from here
Sub Sample()
Dim ws As Worksheet, wsTemp As Worksheet
Dim rRange As Range, rngToCopy As Range
Dim lRow As Long
Dim lRow2 As Long
Dim lCol As Long
'Find the last non-blank cell in column A(1)
lRow2 = Cells(Rows.Count, 1).End(xlUp).Row
'~~> Change this to the relevant sheet
Set ws = ThisWorkbook.Sheets("Combined")
With ws
'~~> Set your range for autofilter
Set rRange = .Range("A1:AR" & lRow2)
'~~> Remove any filters
.AutoFilterMode = False
'~~> Filter, copy visible rows to temp sheet
With rRange
.AutoFilter Field:=9, Criteria1:="X"
'~~> This is required to get the visible range
ws.Rows("1:lRow2").EntireRow.Hidden = True
Set rngToCopy = .SpecialCells(xlCellTypeVisible).EntireRow
Set wsTemp = Sheets.Add
rngToCopy.Copy wsTemp.Range("A1")
'~~> Unhide the rows
ws.Rows("1:lRow").EntireRow.Hidden = False
End With
'~~> Remove any filters
.AutoFilterMode = False
End With
Application.DisplayAlerts = False
wsTemp.Delete
Application.DisplayAlerts = True
End Sub
But I do not know how to modify the With rRange section to meet my needs (i.e, Column I <>"X" and column J=F2
Additionally this line ws.Rows("1:lRow2").EntireRow.Hidden = True is giving me a type mismatch error
UPDATE
So my code now looks like this thanks to this thread
Sub AddFilter()
'
' AddFilter Macro
'
Dim rCrit1 As Range, rCrit2 As Range, rCrit3 As Range
Dim copyRange1 As Range, copyRange2 As Range, copyRange3 As Range
Dim filterRange As Range
Dim lastRow As Long
Set src = ThisWorkbook.Sheets("Combined")
Set tgt = ThisWorkbook.Sheets("Dashboard")
lastRow = src.Range("A" & src.Rows.Count).End(xlUp).Row
Set filterRange = src.Range("A1:Z" & lastRow)
Set copyRange1 = src.Range("A2:A" & lastRow)
Set copyRange2 = src.Range("G2:G" & lastRow)
Set copyRange3 = src.Range("I2:I" & lastRow)
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set rCrit1 = Worksheets("Dashboard").Range("Ref_1")
Set rCrit2 = Worksheets("Dashboard").Range("Ref_2")
Set rCrit3 = Worksheets("Dashboard").Range("Ref_3")
Sheets("Dashboard").Range("A1:C3").ClearContents
Sheets("Dashboard").Range("A1:C3").ClearFormats
Selection.AutoFilter
filterRange.AutoFilter Field:=rCrit1, Criteria1:="<>X"
filterRange.AutoFilter Field:=rCrit2, Criteria1:=rCrit_3
copyRange1.SpecialCells(xlCellTypeVisible).Copy tgt.Range("A5")
copyRange2.SpecialCells(xlCellTypeVisible).Copy tgt.Range("B5")
copyRange3.SpecialCells(xlCellTypeVisible).Copy tgt.Range("C5")
End Sub
However the filterRange.Autofilter line is not reading the rCrit_3 value correctly and so is not filtering based on this (Ref_3 is a named range which contains the YES cell in the first part of the problem).
Additionally the copyRange lines are giving me '1004' runtime error, but if I minimise the spreadsheet and run the code from the VBA window, it will run error free.
Can anyone shed some light on these issues?
