Results 1 to 10 of 10

Find rows and delete them based on multiple criteria

Threaded View

kostas Find rows and delete them... 01-31-2012, 09:07 AM
Kelshaer Re: Find rows and delete them... 01-31-2012, 09:11 AM
kostas Re: Find rows and delete them... 01-31-2012, 09:31 AM
Kelshaer Re: Find rows and delete them... 01-31-2012, 09:39 AM
kostas Re: Find rows and delete them... 01-31-2012, 09:52 AM
Kelshaer Re: Find rows and delete them... 01-31-2012, 09:52 AM
kostas Re: Find rows and delete them... 01-31-2012, 10:02 AM
Kelshaer Re: Find rows and delete them... 01-31-2012, 10:15 AM
kostas Re: Find rows and delete them... 01-31-2012, 10:40 AM
Kelshaer Re: Find rows and delete them... 01-31-2012, 11:59 AM
  1. #1
    Forum Contributor
    Join Date
    03-21-2007
    Posts
    118

    Find rows and delete them based on multiple criteria

    Hi, i've searched the forum but couldn't find exactly what I'm after.

    I have a macro that looks into a worksheet, filters a column and has to delete rows.

    I want to delete those rows based on a set of criteria. Since the criteria have to do with Time, I need it to be over xx:00:00 or under xx:00:00 etc.

    Below is the macro and the criteria I want to plug in the "strCriteria =" bit is:
    delete all rows where the value is: ">19:00:00 and <07:00:00" or ">10:00:00 and <16:00:00", so basically keep only the rows with data between the hours 7-10am and 4-7pm (I have the hours in number format, just wrote them here as hours to make it simple to understand).

    Any suggestions will be greatly appreciated.

    macro:
    Option Explicit

    Sub FilterData()
    ''''''''''''''''''''''''''
    'Written by www.ozgrid.com
    ''''''''''''''''''''''''''

    Dim rRange As Range
    Dim strCriteria As String
    Dim lCol As Long
    Dim rHeaderCol As Range
    Dim xlCalc As XlCalculation
    Const strTitle As String = "OZGRID CONDITIONAL ROW DELETE"

    On Error Resume Next
    Step1:
    'We use Application.InputBox type 8 so user can select range
    Set rRange = Range("a1:d16000")



    Step2:
    'We use Application.InputBox type 1 so return a number
    lCol = 2

    Step3:
    'We use default InputBox type as we want Text
    strCriteria = Range("c3")


    'Store current Calculation then switch to manual.
    'Turn off events and screen updating
    With Application
    xlCalc = .Calculation
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .ScreenUpdating = False
    End With


    'Remove any filters
    ActiveSheet.AutoFilterMode = False

    With rRange 'Filter, offset(to exclude headers) and delete visible rows
    .AutoFilter Field:=lCol, Criteria1:=strCriteria
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    'Remove any filters
    ActiveSheet.AutoFilterMode = False

    'Revert back
    With Application
    .Calculation = xlCalc
    .EnableEvents = True
    .ScreenUpdating = True
    End With
    On Error GoTo 0
    End Sub
    Last edited by kostas; 01-31-2012 at 11:46 AM.
    _-= Have you google'd your question before posting? =-_
    _-= Have you Searched the forum for an answer before posting? =-_

Thread Information

Users Browsing this Thread

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

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