Results 1 to 10 of 10

Thread: How to filter and delete rows of data based on user-specified date
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to filter and delete rows of data based on user-specified date

    Hi everyone,

    Iím having some trouble here, would love to have some feedback on the code I have so far. I have data that Iím trying to filter based on the start date and end date. The Start and End date have their own columns with the rest of the data in, ďMaster Publisher Content.Ē Start date is in Column G and End date is in Column H. Iím trying to filter and delete the rows of data based on a user-specified date range on the ďEnter InfoĒ tab, the user-specified start date is in cell C2 and the user-specified end date is in column E2.

    For example, if cell C2 is April 1st, 2019 and cell E2 is April 30th, 2019 I want to delete the rows of data that are not between these dates.

    Thanks in advance!!


    Option Explicit
    Public Sub DeleteRowsWithAutofilterDates()


    Dim wksData As Worksheet
    Dim lngLastRow As Long
    Dim rngData As Range
    Dim StartDate As Long
    Dim EndDate As Long
    'Set references up-front
    Set wksData = ThisWorkbook.Worksheets("Master Publisher Content")
    Set StartDate = ThisWorkbook.Worksheets("Enter Info").Range("C2")
    Set EndDate = ThisWorkbook.Worksheets("Enter Info").Range("E2")

    'Identify the last row and use that info to set up the Range
    With wksData
    lngLastRow = .Range("G" & .Rows.Count).End(xlUp).Row
    Set rngData = .Range("G2:G" & lngLastRow)
    End With

    '--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Application.DisplayAlerts = False
    With rngData

    'Apply the Autofilter method to the first column of


    .AutoFilter Field:=1, _
    Criteria1:="<=EndDate", _
    Operator:=xlAnd, _
    Criteria2:="=>StartDate"

    'Delete the visible rows while keeping the header
    .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Rows.Delete
    End With



    '--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Application.DisplayAlerts = True

    'Turn off the AutoFilter
    With wksData
    .AutoFilterMode = False
    If .FilterMode = True Then
    .ShowAllData
    End If
    End With



    End Sub

  2. #2
    Board Regular
    Join Date
    Mar 2013
    Posts
    668
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to filter and delete rows of data based on user-specified date

    Declare dates as date
    The header row is included in the filter range and you need to be sure there actually is something to delete or it'll error.
    try this, it assumes headers to be in row 1
    Code:
    Option Explicit
    
    Public Sub DeleteRowsWithAutofilterDates()
    
    Dim wksData As Worksheet
    Dim rngData As Range
    Dim lngLastRow As Long
    Dim StartDate As Date
    Dim EndDate As Date
    
    'Set references up-front
    Set wksData = ThisWorkbook.Worksheets("Master Publisher Content")
    StartDate = ThisWorkbook.Worksheets("Enter Info").Range("C2")
    EndDate = ThisWorkbook.Worksheets("Enter Info").Range("E2")
    
    
    'Identify the last row and use that info to set up the Range
    With wksData
        lngLastRow = .Range("G" & .Rows.Count).End(xlUp).Row
        Set rngData = .Range("G1:H" & lngLastRow)
    End With
    
    Application.DisplayAlerts = False
    
    With rngData
        'remove any existing filters
        .AutoFilter
        'Apply the Autofilter
        .AutoFilter Field:=1, Criteria1:=">=" & StartDate, Operator:=xlFilterValues
        .AutoFilter Field:=2, Criteria1:="<=" & EndDate, Operator:=xlFilterValues
        'Delete the visible rows while keeping the header
            'only if there are rows to delete otherwise it errors
            If .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1 > 0 Then
                .Offset(1).Resize(lngLastRow - 1).Delete
            Else
                MsgBox "There are no records to delete."
            End If
        'remove existing filters
        .AutoFilter
    End With
    
    Application.DisplayAlerts = True
    End Sub

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to filter and delete rows of data based on user-specified date

    Hi, thank you so much for your help! I really appreciate your time

    This is great so far, except it's doing the opposite of what I would like. I apologize for being unclear, my goal is to keep the rows of data within the user-specified range. For example, if the start date is April 1st and the end date is April 31st, i would like to keep all rows containing that data within that period of time.

    Another note that I did not mention: there are blanks cells in column G, I would like to delete all rows where there are blank cells in Column G

    Thanks in advance!

  4. #4
    New Member
    Join Date
    May 2019
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to filter and delete rows of data based on user-specified date

    For the blanks cell, I've been playing around with this code in the if/elseif statement

    If Columns(1) <> "" Then
    .Offset(1).Resize(lngLastRow - 1).Delete

  5. #5
    Board Regular
    Join Date
    Mar 2013
    Posts
    668
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to filter and delete rows of data based on user-specified date

    Sorry 'bout that, hope I do better next time.
    Are you saying that the end of the data doesn't matter, only that it only starts within that period, or is the entire time to be within that period
    or what about a data that starts before the specified start and finishes after the specified end, it would have data within the period ?
    Last edited by NoSparks; Jun 20th, 2019 at 08:19 PM.

  6. #6
    Board Regular
    Join Date
    Mar 2014
    Posts
    719
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to filter and delete rows of data based on user-specified date

    I believe if you reverse the ">=" Operator in your first Field and the reverse the "<=" Operator in Field 2 you should get the results you require.

    Be sure to test on a copy worksheet.

    This will delete the entire row if Column G contains a Blank

    Code:
    Columns("G").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

  7. #7
    New Member
    Join Date
    May 2019
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to filter and delete rows of data based on user-specified date

    You're the best!!

    "what about a data that starts before the specified start and finishes after the specified end, it would have data within the period ?" <-- this is EXACTLY what I've been struggling with. I'm just trying to keep any row of data that falls within the user specified range. For example, if they put the range April 1st - April 30th but there's data that starts in March 8th and ends in April 1st, I want to keep that row. Or if there's data that starts on April 30th and ends on May 8th, I still would like to keep that data.



  8. #8
    New Member
    Join Date
    May 2019
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to filter and delete rows of data based on user-specified date

    Also, I tried reversing the "<=" in their respective fields and it would delete the rows of data that fall within the date parameters

  9. #9
    Board Regular
    Join Date
    Mar 2013
    Posts
    668
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to filter and delete rows of data based on user-specified date

    OK, so the way I see this now is:
    1) eliminate rows where G is blank,
    2) eliminate rows that end before the specified start
    3) eliminate rows that start after the specified end
    and everything else will be at least partially within the specified dates

    Code:
    Option Explicit
    
    Public Sub DeleteRowsWithAutofilterDates_v2()
    
        Dim wksData As Worksheet
        Dim rngData As Range
        Dim lngLastRow As Long
        Dim StartDate As Date
        Dim EndDate As Date
    
    'Set references up-front
    Set wksData = ThisWorkbook.Worksheets("Master Publisher Content")
    StartDate = ThisWorkbook.Worksheets("Enter Info").Range("C2")
    EndDate = ThisWorkbook.Worksheets("Enter Info").Range("E2")
    
    Application.ScreenUpdating = False
    
    With wksData
        'remove rows where G is blank
        On Error Resume Next    'incase there aren't any
        .Range("G1", Cells(Rows.Count, "G").End(xlUp)).SpecialCells(xlBlanks).EntireRow.Delete
        On Error GoTo 0         're-enable error notification
        'Identify the last row and use that info to set up the Range
        lngLastRow = .Range("G" & .Rows.Count).End(xlUp).Row
        Set rngData = .Range("G1:H" & lngLastRow)
    End With
    
    With rngData
        'remove any existing filters
        .AutoFilter
        
        'Apply the Autofilter for starts AFTER end
        .AutoFilter Field:=1, Criteria1:=">" & EndDate, Operator:=xlFilterValues
        'delete these ones if there are any
            If .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1 > 0 Then
                .Offset(1).Resize(lngLastRow - 1).Delete
            End If
        'remove existing filter
        .AutoFilter
        
        'apply autofilter for ends BEFORE start
        .AutoFilter Field:=2, Criteria1:="<" & StartDate, Operator:=xlFilterValues
        'delete these ones if there are any
            If .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1 > 0 Then
                .Offset(1).Resize(lngLastRow - 1).Delete
            End If
        'remove existing filters
        .AutoFilter
    End With
    
    Application.ScreenUpdating = True
    
    End Sub

  10. #10
    New Member
    Join Date
    May 2019
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to filter and delete rows of data based on user-specified date

    I've been trying to troubleshoot this myself, but now for some reason it isn't deleting the rows with blanks in Column G, but everything else is working exactly the way I would like it to!

    Small thing, but was wondering if this is possible - when I run the macro I get a prompt that says "Delete Entire Sheet Row," which makes me click it twice before it deletes anything. Is there a way to just delete the sheet rows automatically?

    Thanks so much for your help, you're a God send!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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