Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If "text" exists in COL A, keep (quick one)
Hello all
Here's the deal. If "Customer Name:" exists in COL A, keep the row. if "Customer Totals" exists in B, keep those rows too. Delete ALL the rest. "Customer Name:" and "Customer Totals" NEVE appear together on the same row. Here's what I've started with: Dim x As Long For x = 1 To Range("A65536").End(xlUp).Row If Range("A" & x) < "Customer Name:" Then Range("A" & x).EntireRow.Delete Else Range("A" & x).EntireRow.Delete End If Next Of course, this will delete the "Customer Totals" rows before th macro has time to KEEP those rows. Can someone help me merge this wit "keep COL B If Customer Totals exists?" Thanks for reading -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If "text" exists in COL A, keep (quick one)
Dim x As Long
For x = Range("A65536").End(xlUp).Row to 1 step -1 If Range("A" & x) < "Customer Name:" and Range("B" & x) < _ "Customer Totals" Then Range("A" & x).EntireRow.Delete End If Next When deleting rows you should work from the bottom rows back to the top rows. This requires an exact match for the text you are testing for. If it seems to fail, the first place to look is thanks that would make the match fail (trailing spaces, capitalization and so forth). Test on a copy of your workbook/data. -- Regards, Tom Ogilvy "Rubix³ " wrote in message ... Hello all Here's the deal. If "Customer Name:" exists in COL A, keep the row. if "Customer Totals" exists in B, keep those rows too. Delete ALL the rest. "Customer Name:" and "Customer Totals" NEVER appear together on the same row. Here's what I've started with: Dim x As Long For x = 1 To Range("A65536").End(xlUp).Row If Range("A" & x) < "Customer Name:" Then Range("A" & x).EntireRow.Delete Else Range("A" & x).EntireRow.Delete End If Next Of course, this will delete the "Customer Totals" rows before the macro has time to KEEP those rows. Can someone help me merge this with "keep COL B If Customer Totals exists?" Thanks for reading. --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If "text" exists in COL A, keep (quick one)
Hello Tom - thanks for the reply.
I ran that macro, left my desk, and when I came back the entire shee was blank minus 40 misc. rows at the end. Both of the values I' searching for are text formatted, without spaces or anything else tha would impair the logic. Any ideas -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If "text" exists in COL A, keep (quick one)
I apologize! It worked like a charm. I had it searching for
*instances* - not exactness. Thank you Tom. --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text "comparison" operator for "contains" used in an "IF" Function | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
No "Sheet" tab exists on Page Layout. Need to print gridlines.??? | Excel Worksheet Functions | |||
Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) | |||
within a macro how can I suppress the warning pop "A file named xxxx.xls already exists in this location. Do you want to replace it?" | Excel Programming |