You can't do that with a function, you need a Sub for it, something like this:

Sub filterandsort()
Range("A1:A9").Select
Selection.Cut Destination:=Range("A2:A10")
Range("A1").Select
ActiveCell.FormulaR1C1 = "old"
Columns("A:A").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="0", Operator:=xlAnd
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFilter
Range("B1").Select
ActiveCell.FormulaR1C1 = "new"
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Regards,
Stefi


€žDingerz€� ezt Ã*rta:

Thanks was helpful, I have a follow on question about another UDF, can you
tell me how i would make a function lookup a list of values which are in a
column and make a new list with ONLY values greater than 0 in assending
order. For example:

Column A New List in Column B
2 0.134
2.5 0.22
5 2
0 2.5
0 3
0 5
3
0.22
0.134

please help

Thanks


"Stefi" wrote:



€žDingerz€� ezt Ã*rta:

Thanks Stefi,

I am unfamilar with UDF. Do i paste this into a new module?

YES


And type =test()
into the cell i want the answer?

NO

type in the result cell =specconc(A1:J1,A2:J2)

Watch list separator! Use comma (,) or semicolon (;) according to your Excel
version!

Stefi