PDA

View Full Version : [SOLVED] Filter based on cell value



mokhtar
06-06-2015, 11:10 PM
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
06-07-2015, 08:50 AM
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
06-07-2015, 12:24 PM
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
06-08-2015, 12:44 AM
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
06-08-2015, 11:51 AM
You're welcome Mr. Mohtar
Glad I can hep you and thanks for the feedback
Regards