How can I auto filter out "0:00" in excel 2010?

[spittingfire]

Hi there,

I am wondering if someone is able to assist me with the below code

ActiveSheet.Range("$A$1:$Y$993").AutoFilter Field:=17, Criteria1:="0:00"

Recording a macro that I found that I can filter in 0:00 with the above code however what I want in actuality is to have it unchecked.

I tried the below code to no avail

ActiveSheet.Range("$A$1:$Y$993").AutoFilter Field:=17, Criteria1:="<>0:00"

Is there some other code that I can use?

Thanks in advance.

[GTO]

Is "0:00" a string value or an actual time? That is, does the formula bar show 12:00:00 AM?

Presuming for the moment, legitimate times are entered and that there's a header row, I think you have data in columns A:Y. If that is correct, try using a helper column of sorts.

In AA1, enter "Criteria" or another word that is not a field name.

In AA2: =ISNA(MATCH(Q2,{0},0))

Select Q1.

Under the Data tab, choose Advanced (filter).

Choose Filter the list, in-place

If not already selected (and entered in the dialog), enter $Q$1:$Q$993 in List Range:

Enter $AA$1:$AA$2 in Criteria range:

Click the OK button.

Hope that helps and here's an example from MSDN: http://support.microsoft.com/kb/183512

Mark

[mancubus]

it's quite likely that Column Q (or 17) of your table contains strings rather than numbers.

if this is the case you can convert them into numbers by multiplying (or dividing) by 1. (or adding / subtracting 0.)

below worked for me.

    With ActiveSheet
        .AutoFilterMode = False
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A" & .Rows.Count).Value = 1
        .Range("A" & .Rows.Count).Copy
        .Range("Q2:Q" & LastRow).PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
        .Range("Q2:Q" & LastRow).NumberFormat = "h:mm"
        .Range("A1:Y" & LastRow).AutoFilter Field:=17, Criteria1:="<>00:00"
        .Range("A" & .Rows.Count).Clear
        .Range("A1").Activate
    End With
    Application.CutCopyMode = False

[Bob Phillips]

Dates and times in VBA are always a challenge. I tend to force the format like so

    With ActiveSheet
    
        .Range("$A$1:$Y$993").AutoFilter Field:=17, Criteria1:="<>" & Format(0, .Cells(2, 17).NumberFormat)
    End With

[spittingfire]

Thanks for all the suggestions but unfortunately none of them worked for me.

To GTO

the "0:00" is an actual formula in the cell where the value is being returned to.

Not sure if that makes a difference or not. What is odd is if I do the reverse meaning write "ActiveSheet.Range("$A$1:$Y$993").AutoFilter Field:=17, Criteria1:="0:00""
in the macro it does go and only select "0:00" in the auto filter so not sure why it should be treated differently when trying not to include it in a filter.

[mancubus]

pls post your workbook here. you can replace/clear the sensitive data, if any.

we only need colum Q values in order to test the code.

[spittingfire]

Hi mancubus

Please find the sheet attached

[Bob Phillips]

Try this

    With ActiveSheet
    
        .Range("$A$1:$Y$993").AutoFilter Field:=17, Criteria1:=">" & 1 / 10 ^ 10
    End With

[spittingfire]

Thanks xld

That solutions works!!