Filter by specific days, months and years

[Red over White]

I have a dataset between A5:Y3000 (but will be using LR1 = Cells(Rows.Count, *).End(xlUp).Row

Column B is an incomplete column of dates (some are blanks), and cell B3 is a date (dd/mm/yy) upon which I want select specific rows, hiding the rows in which the criteria doesn’t apply.
The criteria are:

• To select all of the rows where the month is the same as that in B5; and
• To select all of the rows where the date (dd) is plus or minus 3 the date in B5
• Only to show the rows where the first two criteria are met, and go back every five years. So if I put in a date of 23/03/24, ranged dates from March will appear, for the years 2024, 2019, 2014, 2009 and so on.
  NB The dates go back to 23/03/1889

Cell B4 is “Date” in case the solution involves auto/advanced/filter

Any help would be appreciated.

 

[DanteAmor]

I didn't understand if your data starts in row 5 or row 6, but it's very simple, just change the row number in this line of the macro:

For i = 6 To UBound(a)

Try:

Sub FilterBySpecificDaysMonthsYears()
  Dim a As Variant
  Dim lr As Long, m As Long, i As Long, y As Long, nMode As Long
  Dim date3 As Date, date5 As Date
  Dim rng As Range
  
  Cells.EntireRow.Hidden = False
  lr = Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  a = Range("A1:Y" & lr).Value
  Set rng = Range("B" & lr + 1)

  date3 = Range("B3").Value     'date B3
  date5 = Range("B5").Value     'date B5
  m = Month(date5)              'month
  y = Year(date5)               'year
  nMode = y Mod 5               'every five years
  
  For i = 6 To UBound(a)
    If IsDate(a(i, 2)) Then
      If a(i, 2) = date3 Or (Month(a(i, 2)) = m And Year(a(i, 2)) Mod 5 = nMode) Then
      Else
        If a(i, 2) >= date5 - 3 And a(i, 2) <= date5 + 3 Then
        Else
          Set rng = Union(rng, Range("B" & i))
        End If
      End If
    Else
      Set rng = Union(rng, Range("B" & i))
    End If
  Next
  rng.EntireRow.Hidden = True
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[Peter_SSs]

NB The dates go back to 23/03/1889

• Actual dates in Excel only go back to 1900 so can you confirm that your column B 'dates' are actually Text values?
• If they are text values then can you also confirm that the year part of the date is "yyyy"? Otherwise if only "yy" it would not be possible to differentiate 1892 from 1992.
• And what about the value in B3 - is that an actual date (numerical) or is it text?
• If the date in B3 was, say, the 2nd of a month can you confirm that you would only look back 1 day and not 3 days so the date stays in the same month? (Similar at or near the end of a month)

 

[Red over White]

I didn't understand if your data starts in row 5 or row 6, but it's very simple, just change the row number in this line of the macro:

For i = 6 To UBound(a)

Try:

Sub FilterBySpecificDaysMonthsYears()
  Dim a As Variant
  Dim lr As Long, m As Long, i As Long, y As Long, nMode As Long
  Dim date3 As Date, date5 As Date
  Dim rng As Range
 
  Cells.EntireRow.Hidden = False
  lr = Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  a = Range("A1:Y" & lr).Value
  Set rng = Range("B" & lr + 1)

  date3 = Range("B3").Value     'date B3
  date5 = Range("B5").Value     'date B5
  m = Month(date5)              'month
  y = Year(date5)               'year
  nMode = y Mod 5               'every five years
 
  For i = 6 To UBound(a)
    If IsDate(a(i, 2)) Then
      If a(i, 2) = date3 Or (Month(a(i, 2)) = m And Year(a(i, 2)) Mod 5 = nMode) Then
      Else
        If a(i, 2) >= date5 - 3 And a(i, 2) <= date5 + 3 Then
        Else
          Set rng = Union(rng, Range("B" & i))
        End If
      End If
    Else
      Set rng = Union(rng, Range("B" & i))
    End If
  Next
  rng.EntireRow.Hidden = True
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

Dante
Thanks for this, it works brilliantly. I was also very surprised how quickly it ran. (There were only three pre-1900 dates so I started the run at row 8 to take them out of the equation).