Selected Answer
Hi Nabila
I think I understand your question (but the title "Filter..." confused me, as did your file which did not include the sample records you refer to).
Your existing sub EditData currrently filters out any records where Active Status (in column G, =9) is "Inactive", via the line in bold in this portion:
With RangeToFilter
.AutoFilter Field:=10, Criteria1:=">=" & CriteriaWildCard, Operator:=xlOr, _
Criteria2:="=-"
.AutoFilter Field:=9, Criteria1:="=Active"
.AutoFilter Field:=14, Criteria1:="=Inpat"
End With
Just remove that line, comment it out like this:
With RangeToFilter
.AutoFilter Field:=10, Criteria1:=">=" & CriteriaWildCard, Operator:=xlOr, _
Criteria2:="=-"
' .AutoFilter Field:=9, Criteria1:="=Active" ' allow InActive records
.AutoFilter Field:=14, Criteria1:="=Inpat"
End With
to get mixed records (in both cases) or change it to:
With RangeToFilter
.AutoFilter Field:=10, Criteria1:=">=" & CriteriaWildCard, Operator:=xlOr, _
Criteria2:="=-"
.AutoFilter Field:=9, Criteria1:="=Inactive" ' only InActive records
.AutoFilter Field:=14, Criteria1:="=Inpat"
End With
to get only Inactive records. You should then get the dates and "No" entries as you describe.
REVISION:
Further to your clarification (or my oversight). the attached revised file only filters only for Inpat by commenting out the TWO filter lines below, keeping only those in bold:
With RangeToFilter
' .AutoFilter Field:=10, Criteria1:=">=" & CriteriaWildCard, Operator:=xlOr, _
Criteria2:="=-"
' .AutoFilter Field:=9, Criteria1:="=Active" ' allows Inactive records too
.AutoFilter Field:=14, Criteria1:="=Inpat"
End With
Then records with a leaving date do appear but this line is added to the loop which sets the Status (column 6) and writes "No" if the status is inactive but the Leaving Date is in the future:
For k = 2 To nwsLastRow
If nws.Cells(k, 6) = "Active" Then
nws.Cells(k, 6) = "Yes"
Else: nws.Cells(k, 6) = "No"
' Overwrite Status if leaving date is in the future
If nws.Cells(k, 14).Value > Date Then nws.Cells(k, 6) = "Yes"
End Ife
If you want to change and select how records are filtered, you might consider creating a VBA UserForm (e.g. with "radio buttons" for Active and Inactive)- Don has a few lessons in the subsection "UserForms in Excel" within the Tutorials page, hyperlinked in the green banner above.
Hope this fixes it for you.