Filtering by three criteria including top ten value
in [Programming]
|
Prev: Custom button image not loading on custom ribbon
Next: Pivot table has source data has exceeded 65536 records type m
From: Thomp on 16 Apr 2010 12:28 This is hopefully a littler better explanation of an earlier post of mine. I need to filter a list of information by first the Year and Product. I then need to know of those now filtered results what are the top ten results by revenue a third column. Have tryed Large functions and it didn't work. Any help would be greatly appreciated. thanks, Also I have attached my spreadsheet as an example
From: joel on 17 Apr 2010 09:03
There was no attachment. the code below uses autofilter to filter the 3 columns. then the code uses specialcells to select the visible rows to get the results. I basically recorded a macro and then editied the macro to simplify the code. Sub Gettop10() ColACriteria = "a" ColBCriteria = "B" LastRow = Range("A" & Rows.Count).End(xlUp).Row Columns("A:C").AutoFilter Columns("A:C").AutoFilter Field:=1, Criteria1:="a" Columns("A:C").AutoFilter Field:=2, Criteria1:="b" Columns("A:C").AutoFilter Field:=3, _ Criteria1:="10", Operator:=xlTop10Items Set top10 = Rows("1:" & LastRow).SpecialCells(Type:=xlCellTypeVisible) top10.Select End Sub -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=196213 http://www.thecodecage.com/forumz |