Setting a Filtered Range

[Andy]

I am struggled to set a Filtered range.
First I set the Filter :-

objSht1.Rows("4:4").AutoFilter
objSht1.Rows("4:4").AutoFilter Field:=2, Criteria1:="Fred"
objSht1.Rows("4:4").AutoFilter Field:=6, Criteria1:="Green"
objSht1.Rows("4:4").AutoFilter Field:=13, Criteria1:="<>0", Operator:=xlAnd

Now I want to set a Range to be that Filtered set. I have tried

Set FilteredRge =
objSht1.AutoFilter.Range.Resize(objSht1.AutoFilter.Range.Rows.Count - 1,
1).Offset(1, 0).Cells.SpecialCells(xlCellTypeVisible)

The idea was to omit the header from the filtered range.

This sort of works but if there are only 2 rows in my filtered range, say
row 45 and row 112, then I want to work through my filtered range and examine
cell values in that range. So, I want the 2nd row of my filted range to be
row 112 in the spreadsheet. If I use FilteredRge(2, 4).Value I get Column D
from row 46 not column D from row 112 as I want.

Can anyone help?

 

[Don Guillett]

Modify to suit. Assumes 4 is your header row. The offset 1 is omitting.

Sub filterem()
lr = Cells(Rows.Count, "a").End(xlUp).Row
With Range(cells(4,"a"),cells(lr,13))
Selection.AutoFilter
.AutoFilter Field:=2, Criteria1:="Fred""
.AutoFilter Field:=6, Criteria1:="Green"
.AutoFilter Field:=13 Criteria1:="<>0"
Set mr = .Offset(1).SpecialCells(xlCellTypeVisible)
End With
mr.Copy Sheets("sheet3").Range("a1")
End Sub

 

[Andy]

I will look at this properly tomorrow but it looks like you have suggested
something that had also occurred to me. You have copied the filtered range to
another sheet so that the set of data can be worked on there. Fine.

Is it not possible though, to avoid copying to another sheet and work
through the filtered range in the original sheet?

What I am really trying to understand is how to refer to the 1st, 2nd, 3rd
etc. rows in a filtered range when, in VBA, you do not know the actuals row
numbers for those cells which are SpecialCells(xlCellTypeVisible).

I basically want to loop through just those rows which satisfy the filter
criteria. I thought that by using a pre-filter, it would be much more
efficient than looping through all rows in the worksheet and checking each
row for the criteria values.

Thanks again.

 

[Don Guillett]

Since you did NOT say what you wanted to do with the range I just threw that
in to show what COULD be done. You still don't say what you really want. If
desired, send your file to my address below along with a copy of this msg
and a CLEAR explanation of what you do want.

 

[Andy]

Sorry for confusion

Take data set :-

ColA ColB ColC
blue 9 Tuesday
red 4 Friday
green 8 Monday
red 3 Saturday
red 7 Tuesday


Filter on ColA = "red" and ColB <5

We get

ColA ColB ColC
red 4 Friday
red 3 Saturday

If I get value in 2nd row, Col C of filtered range I would expect "Saturday"
but I get "Monday". The reason, I think, is that Monday is the 2nd row of
full set of data following the first row that meets criteria.

How do I get Saturday without copying data to another sheet? Your copy
method would work actually.

Hope this helps.

 

[Don Guillett]

Send me your file along with this and what you have done so I don't have to
reconstruct

 

[Andy]

Figured it out (assuming Column headings in row 4), code is :-

Dim FilterColC
Dim fc, ii, rn As Integer

ActiveSheet.Rows("4:4").AutoFilter
ActiveSheet.Rows("4:4").AutoFilter Field:=1, Criteria1:="red"
ActiveSheet.Rows("4:4").AutoFilter Field:=2, Criteria1:="<5", Operator:=xlAnd

fc = ActiveSheet.AutoFilter.Range.Rows.Count
ii = 1
rn = 1
For Each rw In ActiveSheet.AutoFilter.Range.Rows.Offset(1) ' Offset skips
visible header
If rw.EntireRow.Hidden = False Then
If ii <> fc Then
FilterColC = rw.Cells(1, 3).Value
MsgBox "Filtered Row" & rn & " ColC = " & FilterColC
rn = rn + 1
End If
End If
ii = ii + 1
Next

ActiveSheet.Rows("4:4").AutoFilter


Thanks anyway.

 

[Don Guillett]

OR (Also gives the actual row)

Sub filterem()
Dim lr As Long
Dim mr, r As Range
lr = Cells(Rows.Count, "a").End(xlUp).Row
With Range(Cells(4, "a"), Cells(lr, 3))
.AutoFilter Field:=1, Criteria1:="red"
.AutoFilter Field:=2, Criteria1:="<5"
Set mr = .Offset(1).Columns(3). _
SpecialCells(xlCellTypeVisible)
For Each r In mr
If r <> "" Then MsgBox "Row " & r.Row & " col C=" & r
Next
.AutoFilter
End With
End Sub