Hi guys,
I have this code and all is working except if there is blank in the criteria range.
2 things:
1) From the code blow, on sheet1 the worksheetfunction.count, if the range is blank and nothing entered, my filtered results in blank. Can not figure how to fix this.
2) I want to add another criteria to filter out any entries that is within the last 7 days on Sheet2, field:=8
I have this code and all is working except if there is blank in the criteria range.
2 things:
1) From the code blow, on sheet1 the worksheetfunction.count, if the range is blank and nothing entered, my filtered results in blank. Can not figure how to fix this.
2) I want to add another criteria to filter out any entries that is within the last 7 days on Sheet2, field:=8
VBA Code:
Sub filter()
On Error Resume Next
Dim scount As Integer, fcount As Integer, shcount As Integer, dcount As Integer, stcount As Integer, tcount As Integer, dacount As Integer
Dim sitelist As Variant, fclmlist As Variant, shiftlist As Variant, deptlist As Variant, statuslist As Variant, typelist As Variant, dalist As Variant
Sheet1.Activate
scount = WorksheetFunction.CountA(Range("H5", Range("H5").End(xlDown)))
shcount = WorksheetFunction.CountA(Range("I5", Range("I5").End(xlDown)))
dcount = WorksheetFunction.CountA(Range("J5", Range("J5").End(xlDown)))
fcount = WorksheetFunction.CountA(Range("K5", Range("K5").End(xlDown)))
stcount = WorksheetFunction.CountA(Range("L5", Range("L5").End(xlDown)))
tcount = WorksheetFunction.CountA(Range("M5", Range("M5").End(xlDown)))
Set scell = ThisWorkbook.Worksheets("Dashboard").Range("H5")
Set shcell = ThisWorkbook.Worksheets("Dashboard").Range("I5")
Set dcell = ThisWorkbook.Worksheets("Dashboard").Range("J5")
Set fcell = ThisWorkbook.Worksheets("Dashboard").Range("K5")
Set stcell = ThisWorkbook.Worksheets("Dashboard").Range("L5")
Set tcell = ThisWorkbook.Worksheets("Dashboard").Range("M5")
sitelist = Range(Cells(5, 8), Cells(scount, 8)).Value
sitelist = Application.Transpose(sitelist)
sitelist = Join(sitelist, ",")
sitelist = Split(sitelist, ",")
shiftlist = Range(Cells(5, 9), Cells(shcount, 9)).Value
shiftlist = Application.Transpose(shiftlist)
shiftlist = Join(shiftlist, ",")
shiftlist = Split(shiftlist, ",")
deptlist = Range(Cells(5, 10), Cells(dcount, 10)).Value
deptlist = Application.Transpose(deptlist)
deptlist = Join(deptlist, ",")
deptlist = Split(deptlist, ",")
fclmlist = Range(Cells(5, 11), Cells(fcount, 11)).Value
fclmlist = Application.Transpose(fclmlist)
fclmlist = Join(fclmlist, ",")
fclmlist = Split(fclmlist, ",")
statuslist = Range(Cells(5, 12), Cells(stcount, 12)).Value
statuslist = Application.Transpose(statuslist)
statuslist = Join(statuslist, ",")
statuslist = Split(statuslist, ",")
typelist = Range(Cells(5, 13), Cells(tcount, 13)).Value
typelist = Application.Transpose(typelist)
typelist = Join(typelist, ",")
typelist = Split(typelist, ",")
With Sheet2
.Range("A1:I1").AutoFilter field:=1, Criteria1:=sitelist
.Range("A1:I1").AutoFilter field:=3, Criteria1:=statuslist
.Range("A1:I1").AutoFilter field:=4, Criteria1:=shiftlist
.Range("A1:I1").AutoFilter field:=5, Criteria1:=deptlist
.Range("A1:I1").AutoFilter field:=6, Criteria1:=fclmlist
.Range("A1:I1").AutoFilter field:=7, Criteria1:=typelist
End With
End Sub