manipulating results of autofilter in vba
i'm working on an inventory tracking sheet that has a snapshot of data
for the current week and the week before. it tracks changes in inventory assignment. i have already built an array that has each unique id for the inventory that is being tracked. i then want to loop the array applying an autofilter on the sheet with the criteria based on the array data. the result of the autofilter (not counting the header) either 1 row (the item has been removed from inventory) or two rows (the data is still in inventory, need to check if it has been reassigned). my problem is that i can't seem to test for how many rows resulted in the autofilter. what i have so far: for xCtr = 1 to totalSerials ActiveSheet.UsedRange.Select Selection.AutoFilter field:=2, Criteria1:=uniqueSerials(xCtr) Set filteredRange = selection.SpecialCells(xlCellTypeVisible) if filteredRange.rows.count = 1 then filteredRange.cells(2,colException).value = "x" 'row 2 because the first row is the header end if if filteredRange.rows.count = 2 then if filteredRange.cells(2,colName).value < _ filteredRange.cells(3, colName).value then filteredRange.cells(2, colException).value = "x" filteredRange.cells(3, colException).value = "x" end if end if selection.autofilter next xCtr then i'll sort the sheet based on the exception column the first time the macro is ran it works fine, afterwards filteredRange.rows.count returns 1 everytime. i get the same results with filteredRange.specialcells(xlCellTypeVisible).coun t i have tried assigning filteredRange different ways as well with the same results. do i even need filteredRange? if not how should i proceed? if so, how can i get it to work as desired? i've read about 10 pages of google searching autofilter in the newsgroup and can't seem to find what i need. copying the rows to another sheet would make the macro run too long and is not desired since i want to sort the results of the conditionals above. TIA bst |
manipulating results of autofilter in vba
Try this:
Option Explicit Sub testme01() Dim myRng As Range Set myRng = ActiveSheet.Range("a1,a3").EntireRow MsgBox myRng.Address & vbLf & myRng.Rows.Count Set myRng = ActiveSheet.Range("a1:a5,a7").EntireRow MsgBox myRng.Address & vbLf & myRng.Rows.Count End Sub You'll see that the .rows.count returns the number of rows in the first area--not the number of rows in the entire range. Instead, you can look at a single column in that autofiltered range and count the number of visible cells. If that number is 1, then only the headers are visible (no real data). If you want the number of rows that are visible (not counting the header), just subtract 1 from the quantity of visible cells in that column. Dim vRng as range dim vRows as long with worksheets("Sheet999").autofilter.range set vrng = .columns(1).cells.specialcells(xlcelltypevisible) vrows = vrng.cells.count - 1 select case vrows case is = 0 'shouldn't happen in your case! case is = 1 'what should happen here case is = 2 'what should happen here case is 2 msgbox "oh, oh!" end select end with (Untested, uncompiled. Watch for typos.) bst wrote: i'm working on an inventory tracking sheet that has a snapshot of data for the current week and the week before. it tracks changes in inventory assignment. i have already built an array that has each unique id for the inventory that is being tracked. i then want to loop the array applying an autofilter on the sheet with the criteria based on the array data. the result of the autofilter (not counting the header) either 1 row (the item has been removed from inventory) or two rows (the data is still in inventory, need to check if it has been reassigned). my problem is that i can't seem to test for how many rows resulted in the autofilter. what i have so far: for xCtr = 1 to totalSerials ActiveSheet.UsedRange.Select Selection.AutoFilter field:=2, Criteria1:=uniqueSerials(xCtr) Set filteredRange = selection.SpecialCells(xlCellTypeVisible) if filteredRange.rows.count = 1 then filteredRange.cells(2,colException).value = "x" 'row 2 because the first row is the header end if if filteredRange.rows.count = 2 then if filteredRange.cells(2,colName).value < _ filteredRange.cells(3, colName).value then filteredRange.cells(2, colException).value = "x" filteredRange.cells(3, colException).value = "x" end if end if selection.autofilter next xCtr then i'll sort the sheet based on the exception column the first time the macro is ran it works fine, afterwards filteredRange.rows.count returns 1 everytime. i get the same results with filteredRange.specialcells(xlCellTypeVisible).coun t i have tried assigning filteredRange different ways as well with the same results. do i even need filteredRange? if not how should i proceed? if so, how can i get it to work as desired? i've read about 10 pages of google searching autofilter in the newsgroup and can't seem to find what i need. copying the rows to another sheet would make the macro run too long and is not desired since i want to sort the results of the conditionals above. TIA bst -- Dave Peterson |
manipulating results of autofilter in vba
Hi Dave,
the second option does not seem to work. the first with line fails, nor does it allow me to set the autofilter criteria. i believe you are leading me in the right direction, but it seems as if i need more detail. i will continue to follow this lead. if you or anyone else seems to have any more insight it would be greatly appreciated. i'm using excel 2003 if that matters. i'm now trying: ..Range("B1").AutoFilter field:=2, Criteria1:=uniqueSerials(xCtr) Set filteredRange = .Cells.SpecialCells(xlCellTypeVisible) i will attempt you column method, but i wonder if it will set my range to just the column filtered, when i infact want to examine manipulate other column's data. from more results i've examined, it is the set filteredRange line that is working incorectly. thanks bst Dave Peterson wrote in : Try this: Option Explicit Sub testme01() Dim myRng As Range Set myRng = ActiveSheet.Range("a1,a3").EntireRow MsgBox myRng.Address & vbLf & myRng.Rows.Count Set myRng = ActiveSheet.Range("a1:a5,a7").EntireRow MsgBox myRng.Address & vbLf & myRng.Rows.Count End Sub You'll see that the .rows.count returns the number of rows in the first area--not the number of rows in the entire range. Instead, you can look at a single column in that autofiltered range and count the number of visible cells. If that number is 1, then only the headers are visible (no real data). If you want the number of rows that are visible (not counting the header), just subtract 1 from the quantity of visible cells in that column. Dim vRng as range dim vRows as long with worksheets("Sheet999").autofilter.range set vrng = .columns(1).cells.specialcells(xlcelltypevisible) vrows = vrng.cells.count - 1 select case vrows case is = 0 'shouldn't happen in your case! case is = 1 'what should happen here case is = 2 'what should happen here case is 2 msgbox "oh, oh!" end select end with (Untested, uncompiled. Watch for typos.) bst wrote: i'm working on an inventory tracking sheet that has a snapshot of data for the current week and the week before. it tracks changes in inventory assignment. i have already built an array that has each unique id for the inventory that is being tracked. i then want to loop the array applying an autofilter on the sheet with the criteria based on the array data. the result of the autofilter (not counting the header) either 1 row (the item has been removed from inventory) or two rows (the data is still in inventory, need to check if it has been reassigned). my problem is that i can't seem to test for how many rows resulted in the autofilter. what i have so far: for xCtr = 1 to totalSerials ActiveSheet.UsedRange.Select Selection.AutoFilter field:=2, Criteria1:=uniqueSerials(xCtr) Set filteredRange = selection.SpecialCells(xlCellTypeVisible) if filteredRange.rows.count = 1 then filteredRange.cells(2,colException).value = "x" 'row 2 because the first row is the header end if if filteredRange.rows.count = 2 then if filteredRange.cells(2,colName).value < _ filteredRange.cells(3, colName).value then filteredRange.cells(2, colException).value = "x" filteredRange.cells(3, colException).value = "x" end if end if selection.autofilter next xCtr then i'll sort the sheet based on the exception column the first time the macro is ran it works fine, afterwards filteredRange.rows.count returns 1 everytime. i get the same results with filteredRange.specialcells(xlCellTypeVisible).coun t i have tried assigning filteredRange different ways as well with the same results. do i even need filteredRange? if not how should i proceed? if so, how can i get it to work as desired? i've read about 10 pages of google searching autofilter in the newsgroup and can't seem to find what i need. copying the rows to another sheet would make the macro run too long and is not desired since i want to sort the results of the conditionals above. TIA bst |
manipulating results of autofilter in vba
The code I suggested is after the range has had the autofilter arrows applied.
And most likely after you've filtered by your criteria. It was in addition to your filtering code--not a replacement for that portion. bst wrote: Hi Dave, the second option does not seem to work. the first with line fails, nor does it allow me to set the autofilter criteria. i believe you are leading me in the right direction, but it seems as if i need more detail. i will continue to follow this lead. if you or anyone else seems to have any more insight it would be greatly appreciated. i'm using excel 2003 if that matters. i'm now trying: .Range("B1").AutoFilter field:=2, Criteria1:=uniqueSerials(xCtr) Set filteredRange = .Cells.SpecialCells(xlCellTypeVisible) i will attempt you column method, but i wonder if it will set my range to just the column filtered, when i infact want to examine manipulate other column's data. from more results i've examined, it is the set filteredRange line that is working incorectly. thanks bst Dave Peterson wrote in : Try this: Option Explicit Sub testme01() Dim myRng As Range Set myRng = ActiveSheet.Range("a1,a3").EntireRow MsgBox myRng.Address & vbLf & myRng.Rows.Count Set myRng = ActiveSheet.Range("a1:a5,a7").EntireRow MsgBox myRng.Address & vbLf & myRng.Rows.Count End Sub You'll see that the .rows.count returns the number of rows in the first area--not the number of rows in the entire range. Instead, you can look at a single column in that autofiltered range and count the number of visible cells. If that number is 1, then only the headers are visible (no real data). If you want the number of rows that are visible (not counting the header), just subtract 1 from the quantity of visible cells in that column. Dim vRng as range dim vRows as long with worksheets("Sheet999").autofilter.range set vrng = .columns(1).cells.specialcells(xlcelltypevisible) vrows = vrng.cells.count - 1 select case vrows case is = 0 'shouldn't happen in your case! case is = 1 'what should happen here case is = 2 'what should happen here case is 2 msgbox "oh, oh!" end select end with (Untested, uncompiled. Watch for typos.) bst wrote: i'm working on an inventory tracking sheet that has a snapshot of data for the current week and the week before. it tracks changes in inventory assignment. i have already built an array that has each unique id for the inventory that is being tracked. i then want to loop the array applying an autofilter on the sheet with the criteria based on the array data. the result of the autofilter (not counting the header) either 1 row (the item has been removed from inventory) or two rows (the data is still in inventory, need to check if it has been reassigned). my problem is that i can't seem to test for how many rows resulted in the autofilter. what i have so far: for xCtr = 1 to totalSerials ActiveSheet.UsedRange.Select Selection.AutoFilter field:=2, Criteria1:=uniqueSerials(xCtr) Set filteredRange = selection.SpecialCells(xlCellTypeVisible) if filteredRange.rows.count = 1 then filteredRange.cells(2,colException).value = "x" 'row 2 because the first row is the header end if if filteredRange.rows.count = 2 then if filteredRange.cells(2,colName).value < _ filteredRange.cells(3, colName).value then filteredRange.cells(2, colException).value = "x" filteredRange.cells(3, colException).value = "x" end if end if selection.autofilter next xCtr then i'll sort the sheet based on the exception column the first time the macro is ran it works fine, afterwards filteredRange.rows.count returns 1 everytime. i get the same results with filteredRange.specialcells(xlCellTypeVisible).coun t i have tried assigning filteredRange different ways as well with the same results. do i even need filteredRange? if not how should i proceed? if so, how can i get it to work as desired? i've read about 10 pages of google searching autofilter in the newsgroup and can't seem to find what i need. copying the rows to another sheet would make the macro run too long and is not desired since i want to sort the results of the conditionals above. TIA bst -- Dave Peterson |
manipulating results of autofilter in vba
well i have made some progress. i can now get an accurate count of the
visible cells with the following turn on filter, and filter set filteredRange = ActiveSheet.AutoFilter.Range filteredRange.columns(1).specialCells(xlcelltypeVi sible).count 'now returns an accurate number of visible rows. now my problem is that i can't figure out a way to access the data in the filtered range. i must not understand the range ojbect very well, but i would i think that with the filteredRange assignment that filteredRange.cells(2,2) or filteredRange.cells(3,2) would give me the visible cells which could be really (22,2) and (790,2), except it gives me the literal cell value as if the sheet was not filtered. i also tried filteredRange.specialcells(xlcelltypevisible).cell s(3,2), yet with the same results. any ideas on how to access the data only in the visible without copying it. thanks for you help so far bst Dave Peterson wrote in : The code I suggested is after the range has had the autofilter arrows applied. And most likely after you've filtered by your criteria. It was in addition to your filtering code--not a replacement for that portion. bst wrote: Hi Dave, the second option does not seem to work. the first with line fails, nor does it allow me to set the autofilter criteria. i believe you are leading me in the right direction, but it seems as if i need more detail. i will continue to follow this lead. if you or anyone else seems to have any more insight it would be greatly appreciated. i'm using excel 2003 if that matters. i'm now trying: .Range("B1").AutoFilter field:=2, Criteria1:=uniqueSerials(xCtr) Set filteredRange = .Cells.SpecialCells(xlCellTypeVisible) i will attempt you column method, but i wonder if it will set my range to just the column filtered, when i infact want to examine manipulate other column's data. from more results i've examined, it is the set filteredRange line that is working incorectly. thanks bst Dave Peterson wrote in : Try this: Option Explicit Sub testme01() Dim myRng As Range Set myRng = ActiveSheet.Range("a1,a3").EntireRow MsgBox myRng.Address & vbLf & myRng.Rows.Count Set myRng = ActiveSheet.Range("a1:a5,a7").EntireRow MsgBox myRng.Address & vbLf & myRng.Rows.Count End Sub You'll see that the .rows.count returns the number of rows in the first area--not the number of rows in the entire range. Instead, you can look at a single column in that autofiltered range and count the number of visible cells. If that number is 1, then only the headers are visible (no real data). If you want the number of rows that are visible (not counting the header), just subtract 1 from the quantity of visible cells in that column. Dim vRng as range dim vRows as long with worksheets("Sheet999").autofilter.range set vrng = .columns(1).cells.specialcells(xlcelltypevisible) vrows = vrng.cells.count - 1 select case vrows case is = 0 'shouldn't happen in your case! case is = 1 'what should happen here case is = 2 'what should happen here case is 2 msgbox "oh, oh!" end select end with (Untested, uncompiled. Watch for typos.) bst wrote: i'm working on an inventory tracking sheet that has a snapshot of data for the current week and the week before. it tracks changes in inventory assignment. i have already built an array that has each unique id for the inventory that is being tracked. i then want to loop the array applying an autofilter on the sheet with the criteria based on the array data. the result of the autofilter (not counting the header) either 1 row (the item has been removed from inventory) or two rows (the data is still in inventory, need to check if it has been reassigned). my problem is that i can't seem to test for how many rows resulted in the autofilter. what i have so far: for xCtr = 1 to totalSerials ActiveSheet.UsedRange.Select Selection.AutoFilter field:=2, Criteria1:=uniqueSerials(xCtr) Set filteredRange = selection.SpecialCells(xlCellTypeVisible) if filteredRange.rows.count = 1 then filteredRange.cells(2,colException).value = "x" 'row 2 because the first row is the header end if if filteredRange.rows.count = 2 then if filteredRange.cells(2,colName).value < _ filteredRange.cells(3, colName).value then filteredRange.cells(2, colException).value = "x" filteredRange.cells(3, colException).value = "x" end if end if selection.autofilter next xCtr then i'll sort the sheet based on the exception column the first time the macro is ran it works fine, afterwards filteredRange.rows.count returns 1 everytime. i get the same results with filteredRange.specialcells(xlCellTypeVisible).coun t i have tried assigning filteredRange different ways as well with the same results. do i even need filteredRange? if not how should i proceed? if so, how can i get it to work as desired? i've read about 10 pages of google searching autofilter in the newsgroup and can't seem to find what i need. copying the rows to another sheet would make the macro run too long and is not desired since i want to sort the results of the conditionals above. TIA bst |
manipulating results of autofilter in vba
dim vRng as range
dim myCell as range with activesheet.autofilter.range 'this returns the first cell in each row that's visible 'by using offset(1,0), we're ignoring the first/header row 'by using .resize(), we're making sure that we get the correct 'rows--(Header - 1 number of rows). set vRng = .columns(1).resize(.rows.count - 1,1).offset(1,0) _ .cells.specialcells(xlcelltypevisible) end with for each mycell in vrng.cells 'I don't know what you want to do when you access these mycell.offset(0,1).value = "This is visible!" next mycell bst wrote: well i have made some progress. i can now get an accurate count of the visible cells with the following turn on filter, and filter set filteredRange = ActiveSheet.AutoFilter.Range filteredRange.columns(1).specialCells(xlcelltypeVi sible).count 'now returns an accurate number of visible rows. now my problem is that i can't figure out a way to access the data in the filtered range. i must not understand the range ojbect very well, but i would i think that with the filteredRange assignment that filteredRange.cells(2,2) or filteredRange.cells(3,2) would give me the visible cells which could be really (22,2) and (790,2), except it gives me the literal cell value as if the sheet was not filtered. i also tried filteredRange.specialcells(xlcelltypevisible).cell s(3,2), yet with the same results. any ideas on how to access the data only in the visible without copying it. thanks for you help so far bst Dave Peterson wrote in : The code I suggested is after the range has had the autofilter arrows applied. And most likely after you've filtered by your criteria. It was in addition to your filtering code--not a replacement for that portion. bst wrote: Hi Dave, the second option does not seem to work. the first with line fails, nor does it allow me to set the autofilter criteria. i believe you are leading me in the right direction, but it seems as if i need more detail. i will continue to follow this lead. if you or anyone else seems to have any more insight it would be greatly appreciated. i'm using excel 2003 if that matters. i'm now trying: .Range("B1").AutoFilter field:=2, Criteria1:=uniqueSerials(xCtr) Set filteredRange = .Cells.SpecialCells(xlCellTypeVisible) i will attempt you column method, but i wonder if it will set my range to just the column filtered, when i infact want to examine manipulate other column's data. from more results i've examined, it is the set filteredRange line that is working incorectly. thanks bst Dave Peterson wrote in : Try this: Option Explicit Sub testme01() Dim myRng As Range Set myRng = ActiveSheet.Range("a1,a3").EntireRow MsgBox myRng.Address & vbLf & myRng.Rows.Count Set myRng = ActiveSheet.Range("a1:a5,a7").EntireRow MsgBox myRng.Address & vbLf & myRng.Rows.Count End Sub You'll see that the .rows.count returns the number of rows in the first area--not the number of rows in the entire range. Instead, you can look at a single column in that autofiltered range and count the number of visible cells. If that number is 1, then only the headers are visible (no real data). If you want the number of rows that are visible (not counting the header), just subtract 1 from the quantity of visible cells in that column. Dim vRng as range dim vRows as long with worksheets("Sheet999").autofilter.range set vrng = .columns(1).cells.specialcells(xlcelltypevisible) vrows = vrng.cells.count - 1 select case vrows case is = 0 'shouldn't happen in your case! case is = 1 'what should happen here case is = 2 'what should happen here case is 2 msgbox "oh, oh!" end select end with (Untested, uncompiled. Watch for typos.) bst wrote: i'm working on an inventory tracking sheet that has a snapshot of data for the current week and the week before. it tracks changes in inventory assignment. i have already built an array that has each unique id for the inventory that is being tracked. i then want to loop the array applying an autofilter on the sheet with the criteria based on the array data. the result of the autofilter (not counting the header) either 1 row (the item has been removed from inventory) or two rows (the data is still in inventory, need to check if it has been reassigned). my problem is that i can't seem to test for how many rows resulted in the autofilter. what i have so far: for xCtr = 1 to totalSerials ActiveSheet.UsedRange.Select Selection.AutoFilter field:=2, Criteria1:=uniqueSerials(xCtr) Set filteredRange = selection.SpecialCells(xlCellTypeVisible) if filteredRange.rows.count = 1 then filteredRange.cells(2,colException).value = "x" 'row 2 because the first row is the header end if if filteredRange.rows.count = 2 then if filteredRange.cells(2,colName).value < _ filteredRange.cells(3, colName).value then filteredRange.cells(2, colException).value = "x" filteredRange.cells(3, colException).value = "x" end if end if selection.autofilter next xCtr then i'll sort the sheet based on the exception column the first time the macro is ran it works fine, afterwards filteredRange.rows.count returns 1 everytime. i get the same results with filteredRange.specialcells(xlCellTypeVisible).coun t i have tried assigning filteredRange different ways as well with the same results. do i even need filteredRange? if not how should i proceed? if so, how can i get it to work as desired? i've read about 10 pages of google searching autofilter in the newsgroup and can't seem to find what i need. copying the rows to another sheet would make the macro run too long and is not desired since i want to sort the results of the conditionals above. TIA bst -- Dave Peterson |
manipulating results of autofilter in vba
Dave,
I got side tracked with some other projects. your method below worked as expected, but not as i needed. i eventually found the solution to my problem my using the areas property(collection?) of the range object. below is my solution. there is probably a more elegant way to do this, but it works :). Thanks for your help and time. €Ž For xCtr = 1 To totalSerials 'set the filter, count how many visible rows there are. set range to just visible rows .Range("B1").AutoFilter field:=2, Criteria1:=uniqueSerials(xCtr) Set filteredRange = ActiveSheet.AutoFilter.Range filteredRowCtr = filteredRange.Columns(1).SpecialCells (xlCellTypeVisible).Count - 1 Set filteredRange = ActiveSheet.AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible) With filteredRange '.Copy Destination:=Worksheets("Sheet2").Range("A1") 'if only one row then the scanner was sent back to corp or is new from corp 'need to confirm what happened with terminal If filteredRowCtr = 1 Then ' MsgBox (xCtr) .Areas(1).Cells(1, colException).Value = "x" End If 'if there are two scanners need to check if the status changed or the owner changed 'if so then to follow up with terminal to confirm If filteredRowCtr = 2 Then 'check status If UCase(.Areas(1).Cells(1, colStatus).Value) < _ UCase(.Areas(2).Cells(1, colStatus).Value) Then 'MsgBox (xCtr) .Areas(1).Cells(1, colException).Value = "x" .Areas(2).Cells(1, colException).Value = "x" End If 'check owner If UCase(.Areas(1).Cells(1, colName).Value) < _ UCase(.Areas(2).Cells(1, colName).Value) Then ' MsgBox (xCtr) .Areas(1).Cells(1, colException).Value = "x" .Areas(2).Cells(1, colException).Value = "x" End If End If 'if there are more than 2 rows exit the script, something is wrong If filteredRowCtr 2 Then MsgBox ("filtered row count is " & filteredRowCtr & ". xCtr is " & xCtr) End If End With 'turn off autofilter Worksheets("Sheet1").AutoFilterMode = False Next xCtr On Thu, 10 Jul 2008 13:12:29 -0500, Dave Peterson wrote: dim vRng as range dim myCell as range with activesheet.autofilter.range 'this returns the first cell in each row that's visible 'by using offset(1,0), we're ignoring the first/header row 'by using .resize(), we're making sure that we get the correct 'rows--(Header - 1 number of rows). set vRng = .columns(1).resize(.rows.count - 1,1).offset(1,0) _ .cells.specialcells(xlcelltypevisible) end with for each mycell in vrng.cells 'I don't know what you want to do when you access these mycell.offset(0,1).value = "This is visible!" next mycell bst wrote: well i have made some progress. i can now get an accurate count of the visible cells with the following turn on filter, and filter set filteredRange = ActiveSheet.AutoFilter.Range filteredRange.columns(1).specialCells(xlcelltypeVi sible).count 'now returns an accurate number of visible rows. now my problem is that i can't figure out a way to access the data in the filtered range. i must not understand the range ojbect very well, but i would i think that with the filteredRange assignment that filteredRange.cells(2,2) or filteredRange.cells(3,2) would give me the visible cells which could be really (22,2) and (790,2), except it gives me the literal cell value as if the sheet was not filtered. i also tried filteredRange.specialcells(xlcelltypevisible).cell s(3,2), yet with the same results. any ideas on how to access the data only in the visible without copying it. thanks for you help so far bst Dave Peterson wrote in : The code I suggested is after the range has had the autofilter arrows applied. And most likely after you've filtered by your criteria. It was in addition to your filtering code--not a replacement for that portion. bst wrote: Hi Dave, the second option does not seem to work. the first with line fails, nor does it allow me to set the autofilter criteria. i believe you are leading me in the right direction, but it seems as if i need more detail. i will continue to follow this lead. if you or anyone else seems to have any more insight it would be greatly appreciated. i'm using excel 2003 if that matters. i'm now trying: .Range("B1").AutoFilter field:=2, Criteria1:=uniqueSerials(xCtr) Set filteredRange = .Cells.SpecialCells(xlCellTypeVisible) i will attempt you column method, but i wonder if it will set my range to just the column filtered, when i infact want to examine manipulate other column's data. from more results i've examined, it is the set filteredRange line that is working incorectly. thanks bst Dave Peterson wrote in : Try this: Option Explicit Sub testme01() Dim myRng As Range Set myRng = ActiveSheet.Range("a1,a3").EntireRow MsgBox myRng.Address & vbLf & myRng.Rows.Count Set myRng = ActiveSheet.Range("a1:a5,a7").EntireRow MsgBox myRng.Address & vbLf & myRng.Rows.Count End Sub You'll see that the .rows.count returns the number of rows in the first area--not the number of rows in the entire range. Instead, you can look at a single column in that autofiltered range and count the number of visible cells. If that number is 1, then only the headers are visible (no real data). If you want the number of rows that are visible (not counting the header), just subtract 1 from the quantity of visible cells in that column. Dim vRng as range dim vRows as long with worksheets("Sheet999").autofilter.range set vrng = .columns(1).cells.specialcells(xlcelltypevisible) vrows = vrng.cells.count - 1 select case vrows case is = 0 'shouldn't happen in your case! case is = 1 'what should happen here case is = 2 'what should happen here case is 2 msgbox "oh, oh!" end select end with (Untested, uncompiled. Watch for typos.) bst wrote: i'm working on an inventory tracking sheet that has a snapshot of data for the current week and the week before. it tracks changes in inventory assignment. i have already built an array that has each unique id for the inventory that is being tracked. i then want to loop the array applying an autofilter on the sheet with the criteria based on the array data. the result of the autofilter (not counting the header) either 1 row (the item has been removed from inventory) or two rows (the data is still in inventory, need to check if it has been reassigned). my problem is that i can't seem to test for how many rows resulted in the autofilter. what i have so far: for xCtr = 1 to totalSerials ActiveSheet.UsedRange.Select Selection.AutoFilter field:=2, Criteria1:=uniqueSerials(xCtr) Set filteredRange = selection.SpecialCells(xlCellTypeVisible) if filteredRange.rows.count = 1 then filteredRange.cells(2,colException).value = "x" 'row 2 because the first row is the header end if if filteredRange.rows.count = 2 then if filteredRange.cells(2,colName).value < _ filteredRange.cells(3, colName).value then filteredRange.cells(2, colException).value = "x" filteredRange.cells(3, colException).value = "x" end if end if selection.autofilter next xCtr then i'll sort the sheet based on the exception column the first time the macro is ran it works fine, afterwards filteredRange.rows.count returns 1 everytime. i get the same results with filteredRange.specialcells(xlCellTypeVisible).coun t i have tried assigning filteredRange different ways as well with the same results. do i even need filteredRange? if not how should i proceed? if so, how can i get it to work as desired? i've read about 10 pages of google searching autofilter in the newsgroup and can't seem to find what i need. copying the rows to another sheet would make the macro run too long and is not desired since i want to sort the results of the conditionals above. TIA bst |
All times are GMT +1. The time now is 02:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com