Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting data from one table/range to another table/range | Excel Discussion (Misc queries) | |||
Problem with Range and Sorting Data | Excel Discussion (Misc queries) | |||
Sorting data by Date | Excel Worksheet Functions | |||
sorting data by date | Excel Worksheet Functions | |||
Sorting data by date | Excel Worksheet Functions |