Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Embedding operators, IF(x="up",,<)IF.. | Excel Discussion (Misc queries) | |||
How do I "Sum sales figure for a finacial month to currenct week"? | Excel Worksheet Functions | |||
Can I automaticly figure "cost" and sale price in Excel? | Excel Worksheet Functions | |||
Help, i can't figure out 2 "And" operators! | Excel Programming |