Avatar of dgd1212
dgd1212 asked on

Macro for filtering by a range of dates

I have a spreadsheet with approximately 10,000 rows of data. Column A has tentative dates and Column B has actual dates.

I need a macro where i can set the lower and upper range of the dates, within the macro not by user input and filter according to that range. Column A and B are not dependent on each other.

Thanks in advance for any suggestions
Microsoft ExcelMicrosoft Applications

Avatar of undefined
Last Comment
dgd1212

8/22/2022 - Mon
krishnakrkc

Hi,

This would give the output if the dates from either Col A or Col B falls within the range.

Sub kTest()
    
    Dim ka, k(), i As Long, n As Long, Flg As Boolean
    Dim dtStart As Date, dtEnd  As Date
    Dim sDt As Long, eDt As Long, UB1 As Long
    
    ka = Intersect(ActiveSheet.UsedRange, Range("a:b")).Value2
    
    dtStart = DateSerial(2000, 1, 1)    'year,month,day
    dtEnd = DateSerial(2012, 3, 1)      'year,month,day
    
    sDt = CLng(dtStart): eDt = CLng(dtEnd)
    UB1 = UBound(ka, 1)
    
    ReDim k(1 To UB1, 1 To 2)
    
    For i = 2 To UB1
        Flg = ((ka(i, 1) >= sDt) * (ka(i, 1) <= eDt)) + ((ka(i, 2) >= sDt) * (ka(i, 2) <= eDt))
        If Flg Then
            n = n + 1
            k(n, 1) = ka(i, 1): k(n, 2) = ka(i, 2)
        End If
    Next
    
    If n Then
        With Range("e2") '<< adjust this range
            .CurrentRegion.Resize(, 2).Offset(1).ClearContents
            .Resize(n, 2) = k
            .Resize(n, 2).NumberFormat = "dd-mmm-yy"
        End With
    End If
    
End Sub

Open in new window


Kris
Patrick Matthews

It's not at all clear what you want.

Do you want to see only rows where both dates fall within your date range, or where at least one of the dates falls within the range?

By "filter", do you mean the standard AutoFilter functionality where the rows are hidden, or do you actually want to remove the rows from the set?
ASKER CERTIFIED SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
dgd1212

Thank You!
Your help has saved me hundreds of hours of internet surfing.
fblack61