Write a function to do what auto-filter does

K

Kobus

Does anybody have a readymade function to do what auto-filter does? I have
done this before but it will take very long to figure it all out. I think I
used a combination of: vlookup, address, index, isna, row().
 
L

Leung

why not just simply use the filter with coding?

1. select the cell
2. select the range of data
3. apply the autofilter

is this what you want?
 
K

Kobus

I had the function working before and it was magic. Auto filter is far
easier but the function has many advantages.
 
B

bj

I assume you want to generate a new filtered table in a different location

if it is a second worksheet
in column A enter
=if(Criteria(sheet1 row),Row(Sheet1 row),"")
copy down for as many rows as you want
in column B enter
=if(row()>count(A:A),"",small(A:A,row())

in Column C
=if(B1="","",index(sheet1-dataset,B1))
and copy down
Hide columns A:B
Modify The Equations As needed for your actual data set
 
K

Kobus

Thanks
I works well, I had it work before in one formula. It was long and very
complicated. If I manage to rework it I will post it under this topic
 
Top