Help, i can't figure out 2 "And" operators!
 

i got code from http://www.mvps.org/dmcritchie/excel/delempty.htm to be
able to delete a row based on the same specific value in another col.

i "attempted" to modify the code to work for my purpose but i've only
been working w/ VBA for a few months so i'm clueless as to what to do
exactly.

the original code is this:

Sub Delete_rows_based_on_ColA_ColB()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = "standard" _
And LCase(rng(i).Offset(0, 1).Value) = "card" _
Then rng(i).EntireRow.Delete
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

and mine is this:

Sub DelRowOn_ColsBCF()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("B").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = "Customer Relations" _
And LCase(rng(i).Offset(0, 1).Value) = "[NULL]" _
And LCase(rng(i).Offset(0, 3).Value) = "[NULL]" _
Then rng(i).EntireRow.Delete
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

what i'm attempting to do is to delete rows in which colB = "Customer
Relations", colC = "[NULL]" and colF = "[NULL]"

i'd appreciate any help,


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Help, i can't figure out 2 "And" operators!
 

I think that this:

If LCase(rng(i).Value) = "Customer Relations" _
And LCase(rng(i).Offset(0, 1).Value) = "[NULL]" _
And LCase(rng(i).Offset(0, 3).Value) = "[NULL]" _

needs to be

If LCase(rng(i).Value) = "customer relations" _
And LCase(rng(i).Offset(0, 1).Value) = "[null]" _
And LCase(rng(i).Offset(0, 4).Value) = "[null]" _

Since you're changing the value to lowercase -- LCase() -- you need to
make sure that you're comparing the values to their lowercase
equivalents. Also, column F is 4 columns offset from B, not 3.

--
HTH,
Dianne



In ,
aapp81 typed:
i got code from http://www.mvps.org/dmcritchie/excel/delempty.htm to
be
able to delete a row based on the same specific value in another col.

i "attempted" to modify the code to work for my purpose but i've only
been working w/ VBA for a few months so i'm clueless as to what to do
exactly.

the original code is this:

Sub Delete_rows_based_on_ColA_ColB()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = "standard" _
And LCase(rng(i).Offset(0, 1).Value) = "card" _
Then rng(i).EntireRow.Delete
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

and mine is this:

Sub DelRowOn_ColsBCF()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("B").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = "Customer Relations" _
And LCase(rng(i).Offset(0, 1).Value) = "[NULL]" _
And LCase(rng(i).Offset(0, 3).Value) = "[NULL]" _
Then rng(i).EntireRow.Delete
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

what i'm attempting to do is to delete rows in which colB = "Customer
Relations", colC = "[NULL]" and colF = "[NULL]"

i'd appreciate any help,


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

Help, i can't figure out 2 "And" operators!
 

unless the cells actually contain the text string [NULL] you will never
pass that test.

if you convert the test cell to lowercase, it can never equal "Customer
Relations" (test should be "customer relations")

rng(i) will always be an offset from the upper left corner of the range.

for example (from the immediate window:)

set rng = Range("A1,A5,A10")
? rng(1).Address
$A$1
? rng(2).Address
$A$2

Since you are using special cells, this will probably not be what you want:


if the actually do contain the text string [NULL]

Sub DelRowOn_ColsBCF()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long, rng1 as Range
Set rng = Columns("B").SpecialCells(xlConstants, xlTextValues)
For each cell in rng
If LCase(cell.Value) = "customer relations" _
And Ucase(cell.Offset(0, 1).Value) = "[NULL]" _
And Ucase(cell.Offset(0, 3).Value) = "[NULL]" _
Then
if rng1 is Nothing then
set rng1 = cell
else
set rng1 = union(cell,rng1)
end if
End if
Next cell
if not rng1 is nothing then _
rng1.Entirerow.Delete
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


--
Regards,
Tom Ogilvy



"aapp81" wrote in message
...

i got code from http://www.mvps.org/dmcritchie/excel/delempty.htm to beSub
DelRowOn_ColsBCF()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long, rng1 as Range
Set rng = Columns("B").SpecialCells(xlConstants, xlTextValues)
For each cell in rng
If LCase(cell.Value) = "customer relations" _
And isempty(cell.Offset(0, 1).Value) _
And isempty(cell.Offset(0, 3).Value) _
Then
if rng1 is Nothing then
set rng1 = cell
else
set rng1 = union(cell,rng1)
end if
End if
Next cell
if not rng1 is nothing then _
rng1.Entirerow.Delete
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

able to delete a row based on the same specific value in another col.

i "attempted" to modify the code to work for my purpose but i've only
been working w/ VBA for a few months so i'm clueless as to what to do
exactly.

the original code is this:

Sub Delete_rows_based_on_ColA_ColB()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = "standard" _
And LCase(rng(i).Offset(0, 1).Value) = "card" _
Then rng(i).EntireRow.Delete
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

and mine is this:

Sub DelRowOn_ColsBCF()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("B").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = "Customer Relations" _
And LCase(rng(i).Offset(0, 1).Value) = "[NULL]" _
And LCase(rng(i).Offset(0, 3).Value) = "[NULL]" _
Then rng(i).EntireRow.Delete
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

what i'm attempting to do is to delete rows in which colB = "Customer
Relations", colC = "[NULL]" and colF = "[NULL]"

i'd appreciate any help,


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

Help, i can't figure out 2 "And" operators!
 

hey!
thank you very much!
i actually did that w/ minimal failure! :D


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Help, i can't figure out 2 "And" operators!
 

hey, thanks a lot, it actually worked great

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Help, i can't figure out 2 "And" operators!
 

Column F is offset(0,4) from Column B as James correctly reported out -
however, the original code isn't spot on with that correction for the case
reasons already pointed out as a minimum. In fact, I don't think Dave's
code (the original original) is correct if specialcells produces a
non-contiguous range.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
unless the cells actually contain the text string [NULL] you will never
pass that test.

if you convert the test cell to lowercase, it can never equal "Customer
Relations" (test should be "customer relations")

rng(i) will always be an offset from the upper left corner of the range.

for example (from the immediate window:)

set rng = Range("A1,A5,A10")
? rng(1).Address
$A$1
? rng(2).Address
$A$2

Since you are using special cells, this will probably not be what you
want:


if the actually do contain the text string [NULL]

Sub DelRowOn_ColsBCF()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long, rng1 as Range
Set rng = Columns("B").SpecialCells(xlConstants, xlTextValues)
For each cell in rng
If LCase(cell.Value) = "customer relations" _
And Ucase(cell.Offset(0, 1).Value) = "[NULL]" _
And Ucase(cell.Offset(0, 3).Value) = "[NULL]" _
Then
if rng1 is Nothing then
set rng1 = cell
else
set rng1 = union(cell,rng1)
end if
End if
Next cell
if not rng1 is nothing then _
rng1.Entirerow.Delete
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


--
Regards,
Tom Ogilvy



"aapp81" wrote in message
...

i got code from http://www.mvps.org/dmcritchie/excel/delempty.htm to
beSub
DelRowOn_ColsBCF()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long, rng1 as Range
Set rng = Columns("B").SpecialCells(xlConstants, xlTextValues)
For each cell in rng
If LCase(cell.Value) = "customer relations" _
And isempty(cell.Offset(0, 1).Value) _
And isempty(cell.Offset(0, 3).Value) _
Then
if rng1 is Nothing then
set rng1 = cell
else
set rng1 = union(cell,rng1)
end if
End if
Next cell
if not rng1 is nothing then _
rng1.Entirerow.Delete
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

able to delete a row based on the same specific value in another col.

i "attempted" to modify the code to work for my purpose but i've only
been working w/ VBA for a few months so i'm clueless as to what to do
exactly.

the original code is this:

Sub Delete_rows_based_on_ColA_ColB()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = "standard" _
And LCase(rng(i).Offset(0, 1).Value) = "card" _
Then rng(i).EntireRow.Delete
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

and mine is this:

Sub DelRowOn_ColsBCF()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("B").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = "Customer Relations" _
And LCase(rng(i).Offset(0, 1).Value) = "[NULL]" _
And LCase(rng(i).Offset(0, 3).Value) = "[NULL]" _
Then rng(i).EntireRow.Delete
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

what i'm attempting to do is to delete rows in which colB = "Customer
Relations", colC = "[NULL]" and colF = "[NULL]"

i'd appreciate any help,


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

Help, i can't figure out 2 "And" operators!
 

this works:

its what i had originally except "Customer R..." is not "cu... r...
and i left "[NULL]" as UCase and put the offset to 4...
other than that, everything was fine in my original code.

thanks for everyone's help!

Sub DelRowOn_ColsBCFmain()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("B").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = "customer relations" _
And UCase(rng(i).Offset(0, 1).Value) = "[NULL]" _
And UCase(rng(i).Offset(0, 4).Value) = "[NULL]" _
Then rng(i).EntireRow.Delete
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Su

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Help, i can't figure out 2 "And" operators!
 

If special cells produces a contiguous range, then it will work. If not,
you are only fooling yourself. It is unwise to use flawed code, it will
eventually jump up and bite you. Suit yourself.

--
Regards,
Tom Ogilvy

"aapp81" wrote in message
...

this works:

its what i had originally except "Customer R..." is not "cu... r..."
and i left "[NULL]" as UCase and put the offset to 4...
other than that, everything was fine in my original code.

thanks for everyone's help!

Sub DelRowOn_ColsBCFmain()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("B").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = "customer relations" _
And UCase(rng(i).Offset(0, 1).Value) = "[NULL]" _
And UCase(rng(i).Offset(0, 4).Value) = "[NULL]" _
Then rng(i).EntireRow.Delete
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

Help, i can't figure out 2 "And" operators!
 

no, please, don't take it the wrong way, i'm not saying your code is
"worse" in my opinion... i'm just a beginner at this so i don't know
much at all... if your code has something that corrects any errors that
may arise then i wouldn't even be able to see the difference - i AM new
to this...

i tried your code, it worked great, the only reason i said the other
code works is b/c i was familiar w/ its structure, that's all...

i didn't mean to come off the wrong way...


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements