+ Reply to Thread
Results 1 to 4 of 4

Delete Rows with Multiple Criteria

  1. #1
    Registered User
    Join Date
    05-27-2011
    Location
    Virginia
    MS-Off Ver
    Excel 365
    Posts
    86

    Delete Rows with Multiple Criteria

    Hi,

    I would like to get assistance with deletion of rows with multiple criteria. I have adapted this code from Ron Debruin.
    Please look at this code to see if it can be more efficient because of the amount of rows that I have, it takes so long.

    [CODE][/
    Sub DeleteRowsVariousCriteria()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
    CalcMode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    End With

    'We use the ActiveSheet but you can replace this with
    'Sheets("MySheet")if you want
    With ActiveSheet

    'We select the sheet so we can change the window view
    .Select

    'If you are in Page Break Preview Or Page Layout view go
    'back to normal view, we do this for speed
    ViewMode = ActiveWindow.View
    ActiveWindow.View = xlNormalView

    'Turn off Page Breaks, we do this for speed
    .DisplayPageBreaks = False

    'Set the first and last row to loop through
    Firstrow = .UsedRange.Cells(1).Row
    Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

    'We loop from Lastrow to Firstrow (bottom to top)
    For Lrow = Lastrow To Firstrow Step -1

    'We check the values in the A column in this example
    With .Cells(Lrow, "a")

    If Not IsError(.Value) Then

    If Not .Value Like "1.1*" And Not .Value Like "1.2*" And Not .Value Like "*ITEM NO:*" Then .EntireRow.Delete
    'This will delete each row with the Value "ron"
    'in Column A, case sensitive.

    End If

    End With

    Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
    .ScreenUpdating = True
    .Calculation = CalcMode
    End With

    End Sub

    CODE]

    Any help would be greatly appreciated.

  2. #2
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: Delete Rows with Multiple Criteria

    Just a tip

    First filter your data with the builtin function then set the RangeTobeDeleted as the visible cells only . Unfilter the data and delete the RangetobeDeleted.

    I assume you have sufficient knowledge of VBA tricks.
    Teach me Excel VBA

  3. #3
    Registered User
    Join Date
    05-27-2011
    Location
    Virginia
    MS-Off Ver
    Excel 365
    Posts
    86

    Re: Delete Rows with Multiple Criteria

    Thanks for your response ImranBhatti. I wanted to include this little piece of code to an additional large amount which I use to create daily reports. I've tried custom filtering but you are limited. Plus I need to add one more criteria.

  4. #4
    Registered User
    Join Date
    05-27-2011
    Location
    Virginia
    MS-Off Ver
    Excel 365
    Posts
    86

    Re: Delete Rows with Multiple Criteria

    I figured another way. I used my code to copy the required criteria to an adjacent cell then deleted the blanks from that column.

    Thanks for you help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Delete Rows after validating for multiple criteria
    By jeevanuae in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-08-2016, 04:56 AM
  2. Replies: 9
    Last Post: 12-20-2012, 10:29 AM
  3. [SOLVED] Delete Rows based on Multiple Name Criteria
    By Graham Taylor in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-14-2012, 09:11 AM
  4. Delete rows. Inputbox multiple criteria
    By vbanooby in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-20-2011, 09:25 AM
  5. Delete Rows using multiple criteria
    By markhao in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-07-2011, 10:18 PM
  6. delete rows with multiple criteria
    By myparticulars in forum Excel General
    Replies: 2
    Last Post: 09-08-2009, 06:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1