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

A

aapp81

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,
 
D

Dianne

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
 
T

Tom Ogilvy

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 said:
i got code from http://www.mvps.org/dmcritchie/excel/delempty.htm to beSub
Click to expand...
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,


------------------------------------------------



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

Tom Ogilvy

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 said:
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 said:
i got code from http://www.mvps.org/dmcritchie/excel/delempty.htm to
Click to expand...
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,


------------------------------------------------



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

aapp81

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
 
T

Tom Ogilvy

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 said:
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


------------------------------------------------



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

aapp81

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help, i can't figure out 2 "And" operators! 2
Delete Row Based On Two Columns 8
Row deletion based on matches 4
Deleting Rows 2
Fails to delete empty rows 4
Object range failed 1
Help with Range Object 1
Slow Delete Blank Rows Macro 5

Top