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.

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"kevin026" wrote in message
...
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.

=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.

--
__________________________________
HTH

Bob

"kevin026" wrote in message
...
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.