Filtering Invoices by Date

morrisgr

Board Regular
Joined
Dec 17, 2005
Messages
50
Can someone please point me in the right direction?
I have a list of 500 invoices dated from Jan1 to Dec31.
I want a Macro to filter the list to include those dated between Jul1 and Sept30 (these dates to be variable and input by the user).

I have a long winded way of doing it by reformatting the dates to "General", which makes them a number, and then comparing each row.
But it checks all 500 each time.
Is it possible to use xlBetween ?

Any guidance appreciated.
morrisg
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
Welcome to MrExcel!

What is the range of your data (what columns)? Do you have a header row?
 

morrisgr

Board Regular
Joined
Dec 17, 2005
Messages
50
Thanks for the welcome, much appreciated.
The total data covers 5columns and 500 rows but the dates are in the Range("A5:A505").
There is a header row.THe Header is in A4 and, not surprisingly, is DATE.
Can you help?
morrisg
 

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
How about this?
Code:
Option Explicit

Sub FilterInvoices()
Dim FilterRange As Range
Dim BegDate As String, EndDate As String
Dim NumberFormat As String

Set FilterRange = Range("A4:A505")
NumberFormat = Range("A5").NumberFormat
On Error GoTo ErrorRoutine
BegDate = CDate(InputBox("Enter beginning date"))
EndDate = CDate(InputBox("Enter ending date"))
FilterRange.AutoFilter Field:=1, Criteria1:=">=" & Format(BegDate, NumberFormat), Operator:=xlAnd _
        , Criteria2:="<=" & Format(EndDate, NumberFormat)
Exit Sub

ErrorRoutine:
    MsgBox "Macro error encountered"
End Sub
 

morrisgr

Board Regular
Joined
Dec 17, 2005
Messages
50

ADVERTISEMENT

Filtering Invoices...a bit more

Hi Barrie.
Excellent.....thanks.
I didn't know how to set Criteria2.....
As I'm sure happens quite a lot in these circumstances, I have a new query.
How can I copy the filtered Rows onto another sheet?
I know how to copy and paste but how do I select the filtered rows?

Thanks for the help, much appreciated.
Morris
 

morrisgr

Board Regular
Joined
Dec 17, 2005
Messages
50
Filtering Invoices....done

Barrie.
Thanks again, I've managed to answer my own question.
Thanks
Morris
 

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
If anyone's interested, here's the code to copy the filtered range:
Code:
FilterRange.Offset(1).Resize(FilterRange.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy _
    Destination:=Sheets("Sheet2").Range("A1")

NOTE - this will create an error if the filtered range is empty.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,521
Messages
5,572,625
Members
412,475
Latest member
JaredNAU
Top