Filter based on cell value

[mokhtar]

Hi everyone
I'm trying to create a code to Filtering data
based on cell value

Range("$A$1:$F$24") Field:= 4

Criteria = drop dowen list on Range("H1") on it dates
any change on Range("H1").Value , ReFilter Data

Thanks in advance for all your help!!
A ـــــــــــــــــــ B ــــــــــــــــــــــــــــC ــــــــــــــــــــــــــــــــــــــــــــــــــــــــــــD ـــــــــــــــــــــــــــــــــ Eــــــــــــــــــــــــــــــــــــــــ F ـــــــــــــــــــــــــــــــــH

num	name	N.N	D.B	D.W	COUNTRY		01/01/2015
1	mokhtar 1	27504092501275	01/01/2015	10/01/2015	EG
2	mokhtar 2	27504092501276	02/01/2015	11/01/2015	EG
3	mokhtar 3	27504092501277	03/01/2015	12/01/2015	EG
4	mokhtar 4	27504092501278	04/01/2015	13/01/2015	EG
5	mokhtar 5	27504092501279	01/01/2015	14/01/2015	EG
6	mokhtar 6	27504092501280	02/01/2015	15/01/2015	EG

[mokhtar]

hi all
I create a code to do this task without Filtering
on my code I used show hide rows based on " H1" value

Private Sub ToggleButton1_Click()

' BY MOKHTAR
' 07/06/2015

If ToggleButton1.Value = False Then
   
   SHOWALL
   ToggleButton1.BackColor = vbRed
   ToggleButton1.Caption = "SHOW FILTER"
   End If
    
If ToggleButton1.Value = True Then
   Hiderowsbasedoncell
   ToggleButton1.BackColor = vbGreen
   ToggleButton1.Caption = "SHOW DATA"
   End If

End Sub

Private Sub SHOWALL()
    Application.ScreenUpdating = False
    Cells.Select
    Selection.EntireRow.Hidden = False
    Range("H1").Select
    Application.ScreenUpdating = True

End Sub
Private Sub Hiderowsbasedoncell()
Dim LastRow As Long, c As Range

LastRow = Cells(Rows.Count, "D").End(xlUp).Row

Application.ScreenUpdating = False

Cells.EntireRow.Hidden = False

For Each c In Range("D2:D" & LastRow)
    If c.Value <> Range("H1").Value Then
      c.EntireRow.Hidden = True
    Else
      c.EntireRow.Hidden = False
    End If
Next

Application.ScreenUpdating = True

End Sub

is there a way to do that but using Filtering or advanced Filter

Anyone interested in giving me a hand with this?

Regards

[YasserKhalil]

Hi Mokhtar
Here's the code

Sub FilterDataByDate()
    Dim WS As Worksheet
    Dim myDate As Date
    
    Set WS = Sheets("Sheet1")
    If IsDate(Range("H1")) Then
        myDate = Range("H1")
        myDate = DateSerial(Year(myDate), Month(myDate), Day(myDate))
    End If
    
    With WS
        .AutoFilterMode = False
        .Range("A1:F1").AutoFilter Field:=5, Criteria1:="=" & myDate, Operator:=xlAnd
    End With
End Sub

Regards

[mokhtar]

Thanks for the reply mr Yasser , I tested the code on my original wb it works exactly as i hoped. but after i changed the Field on your code from 5 to 4 this is a condition on my request . Thanks a lot for your solution and your time, Regards

[YasserKhalil]

You're welcome Mr. Mohtar
Glad I can hep you and thanks for the feedback
Regards