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