| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a Filtered Range
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Setting a Filtered Range
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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Andy" wrote in message ... 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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Setting a Filtered Range
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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Andy" wrote in message ... 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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Andy" wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Setting a Filtered Range
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" wrote: 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Andy" wrote in message ... 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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Andy" wrote in message ... 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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Setting a Filtered Range
Send me your file along with this and what you have done so I don't have to
reconstruct -- Don Guillett Microsoft MVP Excel SalesAid Software "Andy" wrote in message ... 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" wrote: 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Andy" wrote in message ... 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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Andy" wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sum a filtered range | Excel Discussion (Misc queries) | |||
| creating a filtered range/named range | Excel Programming | |||
| setting range().hidden=True causes range error 1004 | Excel Programming | |||
| traversing through a filtered range based on another filtered range | Excel Programming | |||
| filtered range | Excel Programming | |||
Hybrid Mode
