ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Help, i can't figure out 2 "And" operators! (https://www.excelbanter.com/excel-programming/284155-help-i-cant-figure-out-2-operators.html)

aapp81[_14_]

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,

- adam -

James[_14_]

Help, i can't figure out 2 "And" operators!
 
Your code is spot on except for .Offset(0,3), which should be .Offset(0,4) to get from column B to column F

James.

aapp81[_17_]

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

thanks, that does make a big difference... lol


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


All times are GMT +1. The time now is 04:41 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com