Filter Top 5 of Filtered Data

[fisheyecream]

Hi!

I have been trying to retrieve the top 5 entries by revenue after filtering using an industry category. The problem I'm facing is that the top 5 filter seems to assume that I want the data that is not captured by the first filter so I am left with "missing" data which is supposed to actually be caught by the top 5 filter.

Some background: I am trying to write a macro which captures the top 5 industries based on revenue, then copy and paste visible rows into another worksheet. I have managed everything except the top 5 filter. I hope someone can help! Thanks in advance!

 

[Worf]

Hi and welcome to the Board!

One possible solution is to copy the filtered data to another location and filter again:

Sub Filter_filtered_data()
'original table at a1:d35, already filtered by industry


' criteria for top five filtering
Range("o1").Value = ""
Range("o2").FormulaR1C1 = "=RC[-2]>=LARGE(R2C13:R35C13,5)"


' copy filtered data
Range("A1:D35").SpecialCells(xlCellTypeVisible).Copy Destination:=Range("j1")


' apply top five filter
Range("j1:m35").AdvancedFilter Action:=1, CriteriaRange:=Range("o1:o2"), Unique:=False


End Sub

 

[fisheyecream]

Hi worf,

Thanks! That was what I thought too but wasn't sure if there was any other more efficient method of doing so. thanks for your help nevertheless, looks like i'm going to go ahead with the method you suggested!