excel sp2 2003 ,OS: XP
hi all, this was addressed in april 17 by Go6 and has expired for response.
i am having the exact same difficulty and the suggested fix does not seem to work.
[ Menu -> Filter -> Show all vba = ActiveSheet.ShowAllData]
quoting from the post:
I have a list of data in a Worksheet, around 1900 rows and 20 columns. I would like to use AutoFilter to view/analyse this. Two columns are Market Cap (numeric) and Change Today ( Percentage).
I would like to
(a) filter for Market Cap > 120 (using Custom filter)
(b) filter for Top 25 in the "Change Today" column
Given that step (a) results in 300 entries, I thought that step (b) would autofilter these and leave me with 25 rows.
However it actually leaves me with 2 rows. The reason for this seems to be that 23 of the 25 highest entries in "Percentage" column are for rows that have "Market Cap" less than 120, and were already filtered out in step (a)
With wsLines.Range("C6") .AutoFilter Field:=4, Criteria1:="=" & strSeg ActiveSheet.ShowAllData .AutoFilter Field:=17, Criteria1:=">=" & dblLike ActiveSheet.ShowAllData .AutoFilter Field:=18, Criteria1:=">=" & strStartDate, Operator:=xlAnd, Criteria2:="<=" & strEndDate ActiveSheet.ShowAllData .AutoFilter Field:=16, Criteria1:="25", Operator:=xlTop10Items End With
here i filter 3 times, each time reducing the result set which ends up as 41 which i then want the top 25 of and i get 18
in fact the ShowAllData method did not improve the problem at all. in fact it brings back unwanted cells from the first criteria.
ideally i prefer to use these useful MS built in's as i hate having to write tedious code to strip out the top X from a range.