Filtering Invoices by Date

[morrisgr]

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

 

[Barrie Davidson]

Welcome to MrExcel!

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

 

[morrisgr]

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]

How about this?

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]

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]

Filtering Invoices....done

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

 

[Barrie Davidson]

If anyone's interested, here's the code to copy the filtered range:

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.