CODEWITHSUNDEEP
excelvba
Simon Taylor
Simon Taylor

Reputation: 607

Excel Filter Numbers ending in zero

I have an excel column which is formatted as a number with one decimal place. I want to filter only the numberd which end in 0. 1.0 2.0 40.0

When i filter manually using Number ends with 0 - it filters out everything. When i use the following code i get the same (although im using multi criteria). Excel 2016.

Sub ImmediateMain()

With ActiveSheet
        .AutoFilterMode = False
With ActiveSheet.Range("$A$1:$T$200")
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="Immediate"
        .AutoFilter Field:=5, Criteria1:="*0"
End With
End With

End Sub

Upvotes: 1

Views: 2466

Answers (4)

z32a7ul
z32a7ul

Reputation: 3777

Apply Conditional Formatting to your data:

Type: Formula based, Formula: =IF(A2=INT(A2)) Formatting: e.g. Yellow Interior Color

And Filter based on Interior Color.

Upvotes: 0

mojo3340
mojo3340

Reputation: 549

click on the dropdown, under number filter, in the search box type "*.0" then press enter. That is 1 solution

Upvotes: 0

Mladen Savic
Mladen Savic

Reputation: 201

You could add another column (column B), with a formula referencing your existing column (column A):

=A1-floor(A1)

You would then filter the column B where the value is 0.

Upvotes: 1

Gary Evans
Gary Evans

Reputation: 1880

Consider this as an option to the issue.

Add a column to your data and add the below formula for all rows, you could then filter on the result of the formula: -

=IF(ISERR(FIND(".",A1,1)),"Whole","Not whole")

When you store 1.0 in a cell, only 1 is stored, they mean the same thing, but Excel will format it to look like 1.0 if requested (i.e. Show to N decimal places).

The above used find to see if there is a decimal point, if there is not an error is thrown so iserr is used, if there is an error, there was no decimal point.

NOTE: You may want to subsequently copy the new column and paste over itself as 'paste special' > 'values' to make filtering easier.

Upvotes: 0

Related Questions