omit zeroes from a list
 

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.

omit zeroes from a list
 

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.

omit zeroes from a list
 

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