Solved: filtering on named ranges.

[khalid79m]

My code

Private Sub DataType2()
'BY DEPT ONLY
'SITE DATA (SHEET = SITE)
    If Sheets("Site").AutoFilterMode = True Then Sheets("Site").AutoFilterMode = False
    
    If Range("Department_Value") <> "" Then
        Range("Site_Department").AutoFilter Field:=1, Criteria1:=Sheets("Control").Range("C8").Value
        Range("Site_Month").AutoFilter Field:=1, Criteria1:=Sheets("Control").Range("C10").Value
        Range("Site_Year").AutoFilter Field:=1, Criteria1:=Sheets("Control").Range("C12").Value
    End If
End Sub

What am I doing wrong ? I am tring to filter for the 3 criteria's above but it doesnt work.

example with colum ("Site_Department") look for range c8 from the control sheet & with column("Site_Month") look for the range c10 from the control sheet & with the column ("Site_Year") look for the range c12 from the control sheet and then display with results that match all 3 criteria. ..


Im baffled any help would be appreciated

[Bob Phillips]

If they are adjacnet, try

[vba]

Private Sub DataType2()
'BY DEPT ONLY
'SITE DATA (SHEET = SITE)
If Sheets("Site").AutoFilterMode = True Then Sheets("Site").AutoFilterMode = False

If Range("Department_Value") <> "" Then
With Range("Site_Department").Resize(, 3)

.AutoFilter Field:=1, Criteria1:=Sheets("Control").Range("C8").Value
.AutoFilter Field:=2, Criteria1:=Sheets("Control").Range("C10").Value
.AutoFilter Field:=3, Criteria1:=Sheets("Control").Range("C12").Value
End With
End If
End Sub
[/vba]

[khalid79m]

They are not adjecent , can anyone else help ?

[Bob Phillips]

Then you need to resize accordingly and set the field index accordingly.

[khalid79m]

Hi XLD,

Could you give me some help around this, I have never done resize before,

The range "site department" is Column B, the data is held from b1 downwards.


The range "site MONTH" is Column D, the data is held from b1 downwards.
The range "site YEAR" is Column E, the data is held from b1 downwards.

Im really struggling with this so all your help is much appreciated

[Bob Phillips]

You resize by 4 columns, B-E, and you filter on fields 1, 3 and 4

[vba]

Private Sub DataType2()
'BY DEPT ONLY
'SITE DATA (SHEET = SITE)
If Sheets("Site").AutoFilterMode = True Then Sheets("Site").AutoFilterMode = False

If Range("Department_Value") <> "" Then
With Range("Site_Department").Resize(, 4)

.AutoFilter Field:=1, Criteria1:=Sheets("Control").Range("C8").Value
.AutoFilter Field:=3, Criteria1:=Sheets("Control").Range("C10").Value
.AutoFilter Field:=4, Criteria1:=Sheets("Control").Range("C12").Value
End With
End If
End Sub
[/vba]

[khalid79m]

your the man !!!! nice one ..

[khalid79m]

ill try out tonight , make sure it works for me

[khalid79m]

ace.. good job