for...next including if function and or function
 

Hi, I do not knwo what is wrong with my code:

sub cleanisup()
Dim i As Long

For i = 1 To 700

If Range(Cells(i, 6)).Value = "long" Or "CR" Then ' if the value is not
long or CR then
ActiveCell.Rows("1:1").EntireRow.Select 'activate the whole row
Selection.Delete Shift:=xlUp ' delete this activated row


End If

Next i ' test next cell



End Sub

for...next including if function and or function
 

The one thing that stands out immediately is your If-Then test. It should
read this way...

If Range(Cells(i, 6)).Value = "long" Or Range(Cells(i, 6)).Value = "CR" Then

Rick


"Ivanl" wrote in message
...
Hi, I do not knwo what is wrong with my code:

sub cleanisup()
Dim i As Long

For i = 1 To 700

If Range(Cells(i, 6)).Value = "long" Or "CR" Then ' if the value is
not
long or CR then
ActiveCell.Rows("1:1").EntireRow.Select 'activate the whole row
Selection.Delete Shift:=xlUp ' delete this activated row


End If

Next i ' test next cell



End Sub

for...next including if function and or function
 

On Wed, 27 Feb 2008 11:37:02 -0800, Ivanl
wrote:

Hi, I do not knwo what is wrong with my code:

sub cleanisup()
Dim i As Long

For i = 1 To 700

If Range(Cells(i, 6)).Value = "long" Or "CR" Then ' if the value is not
long or CR then
ActiveCell.Rows("1:1").EntireRow.Select 'activate the whole row
Selection.Delete Shift:=xlUp ' delete this activated row


End If

Next i ' test next cell



End Sub

You need to start at the bottom of your range and move up; not at the top and
move down.

Also, which is common, you are only selecting ActiveCell to be the base of your
deletion. You never activate a cell, nor do you need to either activate or
select to do this.

Also some syntax errors.

Something like this might work better:

==========================
Sub cleanisup()
Dim i As Long
For i = 700 To 1 Step -1
If Cells(i, 6).Value = "long" Or Cells(i, 6).Value = "CR" Then
Cells(i, 6).EntireRow.Delete
End If
Next i
End Sub
=============================
--ron

for...next including if function and or function
 

Hi Rick,

Thanks for the reply. I am still getting a 1004 runtime error on the line
you pointed out "method range of object global failed"

Also, are there any other shortform ways of referencing the two values?

Thanks for you help,

Ivan

"Rick Rothstein (MVP - VB)" wrote:

The one thing that stands out immediately is your If-Then test. It should
read this way...

If Range(Cells(i, 6)).Value = "long" Or Range(Cells(i, 6)).Value = "CR" Then

Rick


"Ivanl" wrote in message
...
Hi, I do not knwo what is wrong with my code:

sub cleanisup()
Dim i As Long

For i = 1 To 700

If Range(Cells(i, 6)).Value = "long" Or "CR" Then ' if the value is
not
long or CR then
ActiveCell.Rows("1:1").EntireRow.Select 'activate the whole row
Selection.Delete Shift:=xlUp ' delete this activated row


End If

Next i ' test next cell



End Sub

for...next including if function and or function
 

Sorry, I went for the obvious error without reading exactly what your code
was trying to do. If you haven't already done so, read Ron's response to
your original message as he points out the major flaw in your overall logic.

Rick


"Ivanl" wrote in message
...
Hi Rick,

Thanks for the reply. I am still getting a 1004 runtime error on the line
you pointed out "method range of object global failed"

Also, are there any other shortform ways of referencing the two values?

Thanks for you help,

Ivan

"Rick Rothstein (MVP - VB)" wrote:

The one thing that stands out immediately is your If-Then test. It should
read this way...

If Range(Cells(i, 6)).Value = "long" Or Range(Cells(i, 6)).Value = "CR"
Then

Rick


"Ivanl" wrote in message
...
Hi, I do not knwo what is wrong with my code:

sub cleanisup()
Dim i As Long

For i = 1 To 700

If Range(Cells(i, 6)).Value = "long" Or "CR" Then ' if the value is
not
long or CR then
ActiveCell.Rows("1:1").EntireRow.Select 'activate the whole row
Selection.Delete Shift:=xlUp ' delete this activated row


End If

Next i ' test next cell



End Sub

for...next including if function and or function
 

Thanks gents. my answer has been resolved.

"Ron Rosenfeld" wrote:

On Wed, 27 Feb 2008 11:37:02 -0800, Ivanl
wrote:

Hi, I do not knwo what is wrong with my code:

sub cleanisup()
Dim i As Long

For i = 1 To 700

If Range(Cells(i, 6)).Value = "long" Or "CR" Then ' if the value is not
long or CR then
ActiveCell.Rows("1:1").EntireRow.Select 'activate the whole row
Selection.Delete Shift:=xlUp ' delete this activated row


End If

Next i ' test next cell



End Sub

You need to start at the bottom of your range and move up; not at the top and
move down.

Also, which is common, you are only selecting ActiveCell to be the base of your
deletion. You never activate a cell, nor do you need to either activate or
select to do this.

Also some syntax errors.

Something like this might work better:

==========================
Sub cleanisup()
Dim i As Long
For i = 700 To 1 Step -1
If Cells(i, 6).Value = "long" Or Cells(i, 6).Value = "CR" Then
Cells(i, 6).EntireRow.Delete
End If
Next i
End Sub
=============================
--ron

for...next including if function and or function
 

On Wed, 27 Feb 2008 13:52:42 -0800, Ivanl
wrote:

Thanks gents. my answer has been resolved.

You're welcome. I hope what was written here helped.
--ron