Find and Delete Rows based on a range

damaniam · #1 03-11-2014, 07:06 AM

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

Thanks in advance for the help.

Cross posted at http://www.excelforum.com/excel-prog...ml#post3616238

macropod · #2 03-12-2014, 01:23 AM

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

damaniam · #3 03-12-2014, 06:06 AM

Thanks again

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