Results 1 to 7 of 7

Thread: Filter all years date data and delete except current year

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

    Default Filter all years date data and delete except current year

    Hi Friend, I need help for filter all years date data and delete except current year

    Sheets("1_Clarity_REport").Range("E:E").AutoFilter Field:=5, Operator:=xlOr, _
    Criteria1:="<=" & Application.Eoyear(Year, -1), Criteria2:=">" & Application.Eoyear(Year, 0)

  2. #2
    New Member
    Join Date
    Jun 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: need help filter all years date data and delete except current year

    wrong code copy
    my code is
    Dim iYear As String
    ''''''''''''''''''''Working sheet formatting'''''''''''''''''''''''''''''''''''''''''''''
    'Sheets("Working").Select

    iYear = "<" & Year(Date)

    Set wSheetStart = ActiveSheet
    Set rFilterHeads = Range("A1", Range("IV1").End(xlToLeft))

    With wSheetStart
    .AutoFilterMode = False
    rFilterHeads.AutoFilter
    strCriteria = (iYear)
    If strCriteria = vbNullString Then Exit Sub
    rFilterHeads.AutoFilter Field:=5, Criteria1:=strCriteria
    End With

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,326
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: need help filter all years date data and delete except current year

    Try this:

    Code:
    Sub Macro8()
        Dim sh As Worksheet
        Set sh = ActiveSheet
        sh.Range("A1").AutoFilter 5, "<01/01/" & Year(Date), xlOr, ">12/31/" & Year(Date)
        sh.AutoFilter.Range.Offset(1).EntireRow.Delete
        sh.ShowAllData
    End Sub
    Regards Dante Amor

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

    Default Re: need help filter all years date data and delete except current year

    Quote Originally Posted by DanteAmor View Post
    Try this:

    Code:
    Sub Macro8()
        Dim sh As Worksheet
        Set sh = ActiveSheet
        sh.Range("A1").AutoFilter 5, "<01/01/" & Year(Date), xlOr, ">12/31/" & Year(Date)
        sh.AutoFilter.Range.Offset(1).EntireRow.Delete
        sh.ShowAllData
    End Sub

    Thanks Its working

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,326
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: need help filter all years date data and delete except current year

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

  6. #6
    New Member
    Join Date
    Jun 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: need help filter all years date data and delete except current year

    Hi Dente,

    can you suggest and help to clear the issue where I am stuck. I have write the vba code in outlook 365 office , my criteria is I have file which is saved in my systems C:\ Drive. I want when I click on butto In outlook , file attached in new mail and send in my team there is 4 person. my code is working on one mail id not on multiple id,

    Sub Mail_small_Text_Outlook()
    'Working in Office 2000-2010
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim answer As Integer

    Set OutApp =CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)



    strbody = "Hi there" &vbNewLine & vbNewLine & _
    "PFA"

    On Error Resume Next
    With OutMail
    .To = "nkashyap3@metlife.com"
    .CC =""
    .BCC =""
    .Subject ="text file"
    .Body = strbody
    .Attachments.Add("C:\CPF\201809_NO_Amnt.txt") 'ou can add a file like this
    .Display
    answer =MsgBox("Do you want to send an e-mail?", vbYesNo + vbQuestion,"Send Email")
    '
    If answer = vbYes Then
    .Send
    Else
    Exit Sub
    End If
    'If MsgBox("Do youwant to send an e-mail?", vbQuestion + vbYesNo, "Send Email") =vb yes Then
    'or use
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing




  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,326
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: need help filter all years date data and delete except current year

    I don't have 365 I suggest you create a new thread.
    Regards Dante Amor

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
  •