Sorting a date range of data
 

Hi, I hope you can help. I have a worksheet containing
columns of data. I use the following to sort column A in
date order;

Columns("A:H").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Range("A1").Select

But what I would like to do is to display only the data in
a specified range, for example say;

startdate = "01/10/03"
enddate = "15/10/03"

Is there a way I could state a macro that would either
delete all the data outside this range leaving the data I
require, or copy the required data to another worksheet so
that I can then display it from there.

Thanks
Robert

Sorting a date range of data
 

Robert,

After you have sorted your data, you can use the autofilter to filter
your data for your criteria. The code below is based on the
assumption that your dates are in column A, and that the sheet
"Sheet2" is available for pasting the values.

Note that in VBA, dates are in American standard Month/Day/Year, not
Day/Month/Year as in your example.

HTH,
Bernie
MS Excel MVP

Sub Macro1()
Dim StartDate As Date
Dim EndDate As Date
StartDate = DateValue("10/01/03")
EndDate = DateValue("10/15/03")

With Range("A:H")
.AutoFilter _
Field:=1, _
Criteria1:="=" & StartDate, _
Operator:=xlAnd, _
Criteria2:="<=" & EndDate
.SpecialCells(xlCellTypeVisible).Copy _
Worksheets("Sheet2").Range("A1")
.AutoFilter
End With

End Sub


"Robert" wrote in message
...
Hi, I hope you can help. I have a worksheet containing
columns of data. I use the following to sort column A in
date order;

Columns("A:H").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Range("A1").Select

But what I would like to do is to display only the data in
a specified range, for example say;

startdate = "01/10/03"
enddate = "15/10/03"

Is there a way I could state a macro that would either
delete all the data outside this range leaving the data I
require, or copy the required data to another worksheet so
that I can then display it from there.

Thanks
Robert