PDA

View Full Version : [SOLVED] How can I auto filter out "0:00" in excel 2010?



spittingfire
02-04-2015, 08:13 PM
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
02-04-2015, 09:41 PM
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
02-05-2015, 01:42 AM
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
02-05-2015, 02:17 AM
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
02-05-2015, 06:46 AM
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
02-05-2015, 06:49 AM
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
02-05-2015, 07:45 AM
Hi mancubus

Please find the sheet attached

Bob Phillips
02-05-2015, 09:50 AM
Try this


With ActiveSheet

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

spittingfire
02-05-2015, 10:01 AM
Thanks xld

That solutions works!! :)