How to select a perioid between two dates?

[Waimea]

Hi,

in column G I have a range of dates, mostly without breaks.

Date

2021-09-15​

2021-09-15​

2021-09-14​

2021-09-14​

2021-09-14​

2021-09-13​

2021-09-13​

2021-09-13​

2021-09-12​

2021-09-12​

2021-09-12​

How can I easily select a period of dates between to dates? I am trying to use one cell with "From date" and one cell with "To date",

Ex. If I wanted to select all dates between 2021-09-15 and 2021-09-13, and if left blank I would get all dates. And I will get all data in the other columns where the dates match.

From date: 2021-09-13

To date: 2021-09-15

All suggestions are welcome. Perhaps there is a better way using comboboxes or dropdowns for year, month and day? Or slicers?

EDIT:

I spelled "period" wrong in the thread title, perhaps someone could correct it?

 

[Waimea]

Hi jasonb75,

thank you for your reply. I have recorded it and I have the base code to work form.

Sub Macro1()
    
    Dim startDate As Date
    Dim endDate As Date      
    
    
    Columns("G:G").Select
    Selection.AutoFilter
    ActiveSheet.Range("$G$1:$G$4628").AutoFilter Field:=1, Criteria1:= _
        ">=2021-09-01", Operator:=xlAnd, Criteria2:="<=2021-09-15"
        
End Sub

What would you suggest to be the next step?

 

[jasonb75]

What would you suggest to be the next step?

Perhaps read my previous reply again.

 

[Waimea]

Hi jasonb75,

thank you for your reply!

Now I have recorded all the steps you suggested.

 

[Waimea]

Sub IsDateInArray()
    
    Dim startDate As Date
    Dim endDate As Date
    Dim dtDate As Date
    Dim currentDate As Date
                     
    'startDate = Cells(Rows.Count, "G").End(xlUp).Value
    startDate = Cells(100, "G").Value
    endDate = Cells(2, "G").Value
    
    currentDate = #9/15/2021#
            
            
   For dtDate = startDate To endDate
        
        'Debug.Print dtDate
        
   If Cells(dtDate, "G").Row = dtDate Then
        Debug.Print dtDate & (" Date is in daterange")
   
   End If
                          
    Next dtDate
      
    
End Sub

I am trying to check if the current date is in dtDate? Is there a better way to do this?

 

[Tupe77]

I don't know the area that should be copied or where to copy it, so this only select that range.

Sub TS_DateRNG()
Dim FindedRNG As Range
Dim sArr As Variant
Dim tmp As Variant
Dim sDate As Date: sDate = Range("F2").Value
Dim eDate As Date: eDate = Range("F4").Value
Dim iCount As Long: iCount = 1

    Dim sourceRNG As Range, rowLNG As Long
    Set sourceRNG = Range("G2:G" & Cells(Rows.Count, 7).End(xlUp).Row)
  
    If Len(Range("F2").Value) < 1 Or Len(Range("F4").Value) < 1 Then
        sourceRNG.Select
        Exit Sub
    End If
  
  
    sArr = sourceRNG

    For Each tmp In sArr
        If tmp >= sDate And tmp <= eDate Then
            If FindedRNG Is Nothing Then
                Set FindedRNG = sourceRNG.Cells(iCount)
            Else
                Set FindedRNG = Union(FindedRNG, sourceRNG.Cells(iCount))
            End If
        End If
        iCount = iCount + 1
    Next
    FindedRNG.Select
End Sub

 

[Waimea]

Hi Tupe77,

thank you for your reply.

I am trying out your code, in cell F2 I have "2021-09-01" and in cell F4 I have "2021-09-15".

Your code selects the entire column G for me. Perhaps I am doing something wrong?

 

[Waimea]

Hi Tupe77,

I got your code to work, I was using cell F1 instead of F2.

Now your code selects the date range that I am entering in cells F2 and F4.

How would I expand the selection?

 

[Tupe77]

Added posibility to scale selection.

Sub TS_DateRNG()
Dim FindedRNG As Range
Dim sArr As Variant
Dim tmp As Variant
Dim sDate As Date: sDate = Range("F2").Value
Dim eDate As Date: eDate = Range("F4").Value
Dim iCount As Long: iCount = 1
Dim iOffset As Integer, iResize As Integer
iOffset = -5 ' Starting point difference to Column G
iResize = 8 ' How many Columns to select

    Dim sourceRNG As Range, rowLNG As Long
    Set sourceRNG = Range("G2:G" & Cells(Rows.Count, 7).End(xlUp).Row)
  
    If Len(Range("F2").Value) < 1 Or Len(Range("F4").Value) < 1 Then
        sourceRNG.Select
        Exit Sub
    End If
  
  
    sArr = sourceRNG

    For Each tmp In sArr
        If tmp >= sDate And tmp <= eDate Then
            If FindedRNG Is Nothing Then
                Set FindedRNG = Range(sourceRNG.Cells(iCount).Address).Offset(0, iOffset).Resize(1, iResize)
            Else
                Set FindedRNG = Union(FindedRNG, Range(sourceRNG.Cells(iCount).Address).Offset(0, iOffset).Resize(1, iResize))
            End If
        End If
        iCount = iCount + 1
    Next
    FindedRNG.Select
End Sub

 

[Waimea]

Good morning Tupe77,

thank you for your reply.

This is excellent. Thank you very much for your code!

 

[Tupe77]

Thank you for the feedback!
It's nice to hear that we were able to help.