for...next including if function and or function

[Ivanl]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ron Rosenfeld]

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

 

[Ivanl]

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\)]

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]

Thanks gents. my answer has been resolved.

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

 

[Ron Rosenfeld]

Thanks gents. my answer has been resolved.

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