Thanks again Tom. Will try it out on Monday when I'm at work and will report
back.

My sampel data was just an example trying to explain my problem. Normaly my
data runs down to A80 - A140
and the range A1:A10 always has a value so this is the reason for stopping
at row10.

Regards

Alf

"Tom Ogilvy" wrote in message
...
There was an error in the line

set rng = Cells(1,i)

which should have been

set rng = Cells(i,1)

however, even with the change, it won't work on your sample data because
it
stops at row 10 - which is what your original code did. You would need to
change 10 to something less than 7 for you example.

When I did made the above changes, it worked fine on your data in xl97.


Sub Macro1()
'
Dim i As Integer

For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
Set rng = Cells(i, 1)
Debug.Print i, rng.Address, rng.Text, IsEmpty(rng), IsNumeric(rng)
If Not IsEmpty(rng) And IsNumeric(rng) Then
If rng.Value = 0 Then

rng.ClearContents

End If
ElseIf Not IsEmpty(rng) Then
Exit For
End If
Next i

Range("L89") = Range("A" & Rows.Count).End(xlUp).Row

End Sub


--
Regards,
Tom Ogilvy


"alf bryn" wrote in message
...
Thanks for your answer Tom but the macro don't work. It runs without
problems but it does not delite the zero values after the last text value
so
I get the wrong row number in L89.

Perhaps I did not explain my problem properly. In column "A" I have a
mixture of text values and zeros like this.

A1 5ppm
A2 5ppm
A3 MK1
A4 0
A5 0
A6 CD
A7 CD
A8 0
A9 0

Processing this range I want to delite the zeros in A8 and A9 and find
the
last row numer with text(value). In this case it would be row nr 7.
And the A column should like this after processing

A1 5ppm
A2 5ppm
A3 MK1
A4 0
A5 0
A6 CD
A7 CD
A8
A9

"Tom Ogilvy" wrote in message
...
Sub Macro1()
'
Dim i As Integer

For i = Range("A" & Rows.Count).End(xlUp).Row To 10 Step -1
set rng = cells(1,i)
if not isempty(rng) and isnumeric(rng) then
If rng.Value = 0 Then

rng.ClearContents

End If
elseif not isempty(rng) then
exit for
end if
Next i

Range("L89") = Range("A" & Rows.Count).End(xlUp).Row

End Sub

--
Regards,
Tom Ogilvy

"alf bryn" wrote in message
...
Hi

I have in the A column a mixture of text and 0 values. I want to find
the
row number of last row that contains text and delete any row below
that
with
a zero value.

This macro works fine in Excel 2002 (English version)

Sub Macro1()
'
Dim i As Integer

For i = Range("A" & Rows.Count).End(xlUp).Row To 10 Step -1

If Range("A" & Rows.Count).End(xlUp).Value = 0 Then

Range("A" & Rows.Count).End(xlUp).ClearContents

End If

Next i

Range("L89") = Range("A" & Rows.Count).End(xlUp).Row

End Sub

When I run this macro at work (Excel 97 with SP2, Swedish version) the
line

If Range("A" & Rows.Count).End(xlUp).Value = 0 Then
gives
me
an error (Nr 13)


Can anybody help me with this problem?

Alf