Unique Filter Macro (Ignore Zeros)

[lewis1]

Hi,

My data is a column of zero's and dates.

I have a basic unique filter macro to filter the dates. However, the first two results of the filter are two 'zero's. Could you please help me adjust the formula so that it ignores the zero's.

Sub UniqueFilter()


Range("D10:D1258").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("E10"), Unique:=True


End Sub

PS:
I am also open to a formula which can do the job, however, given the number of rows I have found that any formula is very slow.

Kind regards,
Lewis.

 

[venkat1926]

sample data like this

Excel Workbook
	A	B	C	D	E
1	hdng1				hdng1
2	0				0
3	0
4	1
5	2
6	3
7	2
8	1
9	1
10	2
11	3
Sheet1

try this macro (use this for your data)

 Range("A1:A11").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "E1:E2"), CopyToRange:=Range("A14"), Unique:=True

 

[lewis1]

Hi,

Thanks very much. I attempted to add the code. However, it did not work for me. Now, the code is simply returning only zero....

 

[dmt32]

see if this change to your code helps. I have set the CriteriaRange to B1:B2 on your worksheet - amend as required.

Sub UniqueFilter()
    'add criteria
    'assumes D10 is a heading
    Range("B1").Value = Range("D10").Value
    'set filter
    Range("B2").Value = "<>0"
    
    
    Range("D10:D1258").AdvancedFilter Action:=xlFilterCopy, _
                                      CriteriaRange:=Range("B1:B2"), _
                                      CopyToRange:=Range("E10"), _
                                      Unique:=True
End Sub

Dave

 

[venkat1926]

Thanks very much. I attempted to add the code. However, it did not work for me. Now, the code is simply returning only zero....

where did you park the macro. how did you run the macro

after running macro the sheet will be like this see A14 down

Sheet1

*	A	B	C	D	E
1	hdng1				hdng1
2	0				<>0
3	0
4	1
5	2
6	3
7	2
8	1
9	1
10	2
11	3
12	*	*	*	*	*
13	*	*	*	*	*
14	hdng1	*	*	*	*
15	1	*	*	*	*
16	2	*	*	*	*
17	3	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[lewis1]

Hi,

Thanks for your help. I guess I didn't explain how my data should come out. It appears your results are slightly more elaborate than what i am looking.

Given your data above, I am simply looking for the unique data from Range(A1:A11) to paste in cell C1 for example, and that none of the zero's be included in the unique results.

Apologies, I am not sure what the 2 headings you have in your column A are and all the stars...

 

[venkat1926]

main data is from A1 to A11

the result of the advance filter is in

A14 and down.(unique values except 0)

crieria for advanced filter without zero is E1:E2

the stars are blanks.
you can copy A14 anywhere you like
slternatively you can define "copy to rangae:=<change A14 to any o;ther cell)

I do not like to copy the reslt within the data rows sometimes filtered data may give problem particularly if it is autofilter.