omit zeroes from a list

[kevin026]

Say i have a list in a column

A
B
0
0
C
D
0
E

I would like to have a list that is the same as above but omits the zeroes
in another column, without hiding rows.

A
B
C
D
E

But not an autofilter bar, as I'd like for the list to automatically omit
the zeroes without having to mess with an autofilter bar.

Many thanks.

 

[Don Guillett]

try this.

Sub copynonzero()
Lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("A1:a" & Lr).AutoFilter Field:=1, Criteria1:="<>0"
Range("a2:a" & Lr).SpecialCells(xlCellTypeVisible).Copy Range("c1")
Range("A1:a" & Lr).AutoFilter
End Sub

 

[Bob Phillips]

=IF(ISERROR(SMALL(IF($A$1:$A$20<>0,ROW($A$1:$A$20),""),ROW($A1))),"",
INDEX($A$1:$A$20,SMALL(IF($A$1:$A$20<>0,ROW($A$1:$A$20),""),ROW($A1))))

which is an array fomula, so commit with Ctrl-Sift-Enter, not just Enter,
and copy down as far as you need.