vba to filter only for values

[daveyc3000]

in column AJ, i have some text/error/blanks which i dont want copied ("market value", "profit/(loss) in bps", "#value!")...i just want to copy values...is there a way to filter only for values?

 

[ThrottleWorks]

Hi @daveyc3000 , could you please explain further. If possible kindly upload a sample file.

http://forum.chandoo.org/threads/file-upload.12109/

 

[daveyc3000]

sorry cant upload at the moment...i just basically want to filter only for numerical values...i dont want it to filter errors, blanks, and text

 

[ThrottleWorks]

So @daveyc3000 you just need to filter numerical data from the column.

For example, if you are applying filter on Column A, and the values are Y,N,M,1,10,45,WE. You want to filter 1,10,45 only.

Correct me if I am wrong. Give me some time I will check whether I can do it and post the solution.

Else wait till some expert (I am not) help you.

 

[daveyc3000]

throttle, you are correct...only 1,10,45

 

[ThrottleWorks]

Hi @daveyc3000 , will the code mentioned below work ?

It will work only if your values does not have negetive numbers.
I guess you know how to define range so not explaing that part.

I have just recorded this macro. Seems to be working. Will try to post better alternative if succeed.

Sub Macro1()
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$65000").AutoFilter Field:=1, Criteria1:=">0", _
    Operator:=xlAnd
End Sub