Macro problem
 

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

Macro problem
 

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

Macro problem
 

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

Macro problem
 

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

Macro problem
 

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

Macro problem
 

Tried your macro to day and it worked without any probles. Thanks Tom!

Sample data was just an example to explain my problem. My real data range is
A100 - A150 but the first 10 rows has always a value. This is why the count
stopps at line 10. Still my mistake should have adjusted it to fit my
example.

A co-worker of mine told me to swap 0 with None in my original macro:

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 = None Then
Range("A" & Rows.Count).End(xlUp).ClearContents
End If
Next i
Range("L89") = Range("A" & Rows.Count).End(xlUp).Row
End Sub

This I did and this macro now runs without any problem in Excel 97 as well
as in Excel 2002.

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()
'
End SubDim 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



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