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.
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!! :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.