#1
|
|||
|
|||
Find and Delete Rows based on a range
Looking for a macro to delete rows where Column A contains a certain value. I've attached a copy of the worksheet (sanitized to remove any personal info). This will be typical application. The goal here is to delete all row based upon the following information, if a row is found to contain that information in column A otherwise move on down the list. While the number list could possibly contain up to 4k lines, in any given scenario only about 300 lines will be used at most.
5000-5999 Delete 6000-6899 Keep 7000-7499 Keep 7500-8299 Delete 8400-9599 Delete 8300-8399 Keep 9600-9699 Keep 9700-9999 Delete The following macro is one that I have used in the past for a similar idea but not sure how to alter it to run for so many possible numbers. Code:
Sub DeleteExcess() 'THIS DELETES ALL ROWS WHERE THE VALUE IN COLUMN A IS "P" Dim FoundCell As Range Application.ScreenUpdating = False Set FoundCell = Range("A4:A220").Find(what:="p") Do Until FoundCell Is Nothing FoundCell.EntireRow.Delete Set FoundCell = Range("A4:A220").FindNext Loop End Sub Cross posted at http://www.excelforum.com/excel-prog...ml#post3616238 |
#2
|
||||
|
||||
Try:
Code:
Sub DeleteExcess() Application.ScreenUpdating = False Dim lRow As Long, cRow As Long With ActiveSheet lRow = .Cells.SpecialCells(xlCellTypeLastCell).Row For cRow = lRow To 5 Step -1 Select Case .Cells(cRow, 1).Value Case 5000 To 5999, 7500 To 8299, 8400 To 9599, 9700 To 9999 .Cells(cRow, 1).EntireRow.Delete Case Else End Select Next End With Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Thanks again
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Find and Delete Rows | damaniam | Word VBA | 1 | 03-11-2014 06:54 AM |
Delete Range After Bookmark? | VBA_Elfe | Word VBA | 3 | 04-05-2013 05:05 AM |
Word Macro to find and delete rows that contain adjacent cells containing "." | AlexanderJohnWilley | Word VBA | 7 | 11-08-2012 10:15 AM |
Can't delete range - error 5904 | expatriate | Word VBA | 1 | 06-03-2011 12:12 AM |
How to remove blank rows from a specified range? | Learner7 | Excel | 1 | 04-19-2011 02:45 AM |