deleting row using macro need help

[excelguru]

I have two columns in excel.All numbers.

column A ---1,2,3,4,5,6,7,8,9,
column B ---123,0,654,0,23,55,0,0,0

I want the macro to do the following
======================================
Start with column B.If it finds 0 in some row in column B, delete th
row itself in excel.so after runnning the
macro the above data will look as follows.


Output of the macro
=====================
column A ---1,3,5,6
column B ---123,654,23,55

Important: I do this everytime I open an excel spread sheet.Forma
cells-->Category-->Number--
--->4 Decimal Places.


Thank-You for all the help.


excelgur

 

[Tom Ogilvy]

Sub tester3()
Dim rng As Range
Columns(2).Replace What:="0", _
Replacement:="=na()", _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False
On Error Resume Next
Set rng = Columns(2).SpecialCells( _
xlFormulas, xlErrors)
On Error GoTo 0
If Not rng Is Nothing Then
rng.EntireRow.Delete
End If
End Sub

 

[excelguru]

Thank-You Tom, the macro works very well.

I appreciate your help

-excelgur