Results 1 to 2 of 2

Thread: How to filter/delete rows of data based on user 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/delete rows of data based on user 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
    0 0
     

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,962
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    43 Thread(s)

    Default Re: How to filter/delete rows of data based on user date

    Duplicate https://www.mrexcel.com/forum/excel-...ml#post5297414

    Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
    Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10
    0 0
     

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
  •