CODEWITHSUNDEEP
excelvbadate
J Smith
J Smith

Reputation: 21

Excel - filter table results by dates

I have a refreshable table in excel and I want to filter the rows by a couple of date ranges. Each row has a date and other information.

I want to find the rows that are in the first date range (F1:F2) and are not in the second date range (H1:H2).

The table is refreshable, and can change size. It currently spans A3:X6146. The table is a query, so it will change sizes when a separate date range is used to find the table values.

I don't have much VBA experience, so this problem is tripping me up. Any ideas?

Thanks

EDIT:

I'll try to make the issue clearer.

I have a table that is created via a query that pulls in data that falls between the Starting Date and the Ending Date, 1/1/2016 and 12/31/2017 here. It lists each time an item was purchased, so each one can be listed multiple times.

I want to find which items were purchased (listed in the table) between the Active Date Range start and end dates (cells F1 and F2), and NOT purchased between the Inactive Date range (cells H1 and H2).

Starting Date: 1/1/2016    Active Date Range Start: 3/1/2016    Inactive Date Start: 3/2/2017

Ending Date: 12/31/2017    Active Date Range End: 3/1/2017      Inactive Date End: 9/22/2017




item    date
1      9/21/2017
2      9/20/2017
3      9/20/2017

Upvotes: 1

Views: 90

Answers (1)

ChrCury78
ChrCury78

Reputation: 437

Yes, I can say to you what I would do.

Create one additional column to keep the result value, if is in or out of the date range. Then

dim t() as string, lin as long, linf as long
linf=Range("F65536").End(xlUp).Row  'or any other more precise way to get the final line
redim t(1 to linf-2,1 to 1)

'range dates - are they on the worksheet?
dim rg_dates as Range,r as range,b as boolean
set rg_dates=Sheets("xxxx").Range("B1:B4") ' just an example, the range would be B1:C4 but use only the first column - see below

    For lin=3 to linf
        b=False
       For each r in rg_dates
          If cells(lin,"F").value>= r.Cells(1,1) and cells(lin,"G").Value<=r.Cells(1,2).value then
             b=true
             Exit for
          End If
      Next r
      If b then t(lin-2,1)="Y" else t(lin-2,1)="N"

    Next l
   Range("Z3:Z" & linf).Value = T

'Then just filter the table.

There would be then many things to do to keep it error free, and how to apply it at the concrete situation. Hopefully with what I wrote above you can get an idea about things you can do using VBA. If you are using code to filter the table you can do all this invisible to the user, creating an extra column for the filter criteria, filtering, and then deleting the whole column..

Upvotes: 0

Related Questions