Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Help with Sumif Function including dates | Excel Worksheet Functions | |||
SUMPRODUCT with criteria including OR function | Excel Discussion (Misc queries) | |||
lookup function, in which the table's name is including in another | Excel Discussion (Misc queries) | |||
Subtraction when including the MOD() function | Excel Worksheet Functions | |||
Workday function including sat, excluding sun | Excel Worksheet Functions |