Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-11-2014, 07:06 AM
damaniam damaniam is offline Find and Delete Rows based on a range Windows 7 64bit Find and Delete Rows based on a range Office 2013
Novice
Find and Delete Rows based on a range
 
Join Date: Nov 2013
Posts: 18
damaniam is on a distinguished road
Default 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
Thanks in advance for the help.


Cross posted at http://www.excelforum.com/excel-prog...ml#post3616238
Attached Files
File Type: xlsx Sanitary Worksheet.xlsx (16.5 KB, 16 views)
Reply With Quote
  #2  
Old 03-12-2014, 01:23 AM
macropod's Avatar
macropod macropod is offline Find and Delete Rows based on a range Windows 7 32bit Find and Delete Rows based on a range Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 03-12-2014, 06:06 AM
damaniam damaniam is offline Find and Delete Rows based on a range Windows 7 64bit Find and Delete Rows based on a range Office 2013
Novice
Find and Delete Rows based on a range
 
Join Date: Nov 2013
Posts: 18
damaniam is on a distinguished road
Default

Thanks again
Reply With Quote
Reply

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
Find and Delete Rows based on a range Delete Range After Bookmark? VBA_Elfe Word VBA 3 04-05-2013 05:05 AM
Find and Delete Rows based on a range 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
Find and Delete Rows based on a range How to remove blank rows from a specified range? Learner7 Excel 1 04-19-2011 02:45 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:59 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft