Vassen
07-19-2016, 03:26 AM
Hi,
I need help to modify the code below with respect to the following statement: I have dates in column BO4:BO for which below codes works to auto filter between two dates in BO1 and BQ1. In column BS4:BS I have either "E" and "C" or blank. I need to modify code so that when applying the filter in BO it exclude all rows for which there is a "C" in column BS . I need also a code to remove the filter.Thanks for your help
Sub FilterBetweenDates1()
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Dim StartDate As Date, EndDate As Date
Dim FilterStartDate As Date, FilterEndDate As Date
Dim LastRow As Long
Dim FilterRange As Range
StartDate = Range("BO1").Value
EndDate = Range("BQ1").Value
LastRow = _
Cells.Find(What:="*", After:=Range("BO3"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set FilterRange = Range("BO4:BO" & LastRow)
FilterStartDate = _
DateSerial(Year(StartDate), Month(StartDate), Day(StartDate) - 1)
FilterEndDate = _
DateSerial(Year(EndDate), Month(EndDate), Day(EndDate) + 1)
FilterRange.AutoFilter _
Field:=1, Criteria1:=">" & CDbl(FilterStartDate), _
Operator:=xlAnd, _
Criteria2:="<" & CDbl(FilterEndDate)
Set FilterRange = Nothing
Application.ScreenUpdating = True
End Sub
I need help to modify the code below with respect to the following statement: I have dates in column BO4:BO for which below codes works to auto filter between two dates in BO1 and BQ1. In column BS4:BS I have either "E" and "C" or blank. I need to modify code so that when applying the filter in BO it exclude all rows for which there is a "C" in column BS . I need also a code to remove the filter.Thanks for your help
Sub FilterBetweenDates1()
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Dim StartDate As Date, EndDate As Date
Dim FilterStartDate As Date, FilterEndDate As Date
Dim LastRow As Long
Dim FilterRange As Range
StartDate = Range("BO1").Value
EndDate = Range("BQ1").Value
LastRow = _
Cells.Find(What:="*", After:=Range("BO3"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set FilterRange = Range("BO4:BO" & LastRow)
FilterStartDate = _
DateSerial(Year(StartDate), Month(StartDate), Day(StartDate) - 1)
FilterEndDate = _
DateSerial(Year(EndDate), Month(EndDate), Day(EndDate) + 1)
FilterRange.AutoFilter _
Field:=1, Criteria1:=">" & CDbl(FilterStartDate), _
Operator:=xlAnd, _
Criteria2:="<" & CDbl(FilterEndDate)
Set FilterRange = Nothing
Application.ScreenUpdating = True
End Sub