i have column A with the dates from 1 jan 06 to 31 dec 06 and the column next
to it has some data.
how can i do a filter that can sort the data only for the range i want (ie
from 1 jan to 22 apr 06)
thanks

Sub SortData()

Dim StartDate As Date
Dim EndDate As Date
Dim StartRow As Integer
Dim EndRow As Integer
Dim i As Integer

' Allocate cells in which you can specify the
' StartDate and EndDate for your filter (Assumed D1 and D2 here)
' Enter dates into your chosen cells formatted as dates.

StartDate = Range("D1").Value
EndDate = Range("D2").Value

Range("A1").Select
' Find the StartDate position
Do Until ActiveCell.Offset(i, 0).Value = StartDate
i = i + 1
Loop

StartRow = Range("A1").Offset(i, 0).Row

' Reset the counter
i=0
' Find the EndDate position
Do Until ActiveCell.Offset(i, 0).Value = EndDate
i = i + 1
Loop

EndRow = Range("A1").Offset(i, 0).Row

' Now use StartRow and EndRow to sort your data

End Sub

massi wrote:
i have column A with the dates from 1 jan 06 to 31 dec 06 and the column next
to it has some data.
how can i do a filter that can sort the data only for the range i want (ie
from 1 jan to 22 apr 06)
thanks

sorry but i cannot figure out how it works.
if i try to run the macro as normal nothing happens...

"Alan" wrote:


Sub SortData()

Dim StartDate As Date
Dim EndDate As Date
Dim StartRow As Integer
Dim EndRow As Integer
Dim i As Integer

' Allocate cells in which you can specify the
' StartDate and EndDate for your filter (Assumed D1 and D2 here)
' Enter dates into your chosen cells formatted as dates.

StartDate = Range("D1").Value
EndDate = Range("D2").Value

Range("A1").Select
' Find the StartDate position
Do Until ActiveCell.Offset(i, 0).Value = StartDate
i = i + 1
Loop

StartRow = Range("A1").Offset(i, 0).Row

' Reset the counter
i=0
' Find the EndDate position
Do Until ActiveCell.Offset(i, 0).Value = EndDate
i = i + 1
Loop

EndRow = Range("A1").Offset(i, 0).Row

' Now use StartRow and EndRow to sort your data

End Sub

massi wrote:
i have column A with the dates from 1 jan 06 to 31 dec 06 and the column next
to it has some data.
how can i do a filter that can sort the data only for the range i want (ie
from 1 jan to 22 apr 06)
thanks

Massi,

The macro simply looks up the positions of the start row for your sort
and the end row. As it stands it will appear to do nothing but if you
insert a temporary line into the code

msgbox "Start Row = " & StartRow & vbcr & "End Row = " & EndRow

you will be able to see what is happening.

(Insert new code below the line that says "Now use StartRow and EndRow
to sort your data" you will see what it has calculated)

You can now use the start and end references to direct your sort.

Alan

massi wrote:
sorry but i cannot figure out how it works.
if i try to run the macro as normal nothing happens...

"Alan" wrote:


Sub SortData()

Dim StartDate As Date
Dim EndDate As Date
Dim StartRow As Integer
Dim EndRow As Integer
Dim i As Integer

' Allocate cells in which you can specify the
' StartDate and EndDate for your filter (Assumed D1 and D2 here)
' Enter dates into your chosen cells formatted as dates.

StartDate = Range("D1").Value
EndDate = Range("D2").Value

Range("A1").Select
' Find the StartDate position
Do Until ActiveCell.Offset(i, 0).Value = StartDate
i = i + 1
Loop

StartRow = Range("A1").Offset(i, 0).Row

' Reset the counter
i=0
' Find the EndDate position
Do Until ActiveCell.Offset(i, 0).Value = EndDate
i = i + 1
Loop

EndRow = Range("A1").Offset(i, 0).Row

' Now use StartRow and EndRow to sort your data

End Sub

massi wrote:
i have column A with the dates from 1 jan 06 to 31 dec 06 and the column next
to it has some data.
how can i do a filter that can sort the data only for the range i want (ie
from 1 jan to 22 apr 06)
thanks

Hi Alan,
I have tried to put this after the code you gave me but it doesn't work. I
have also tried to put
StartDate instead StartRow but the result is the same...

Rows("StartRow:EndRow").Select
Selection.Copy
Sheets("Sheet3").Select
Sheets.Add
ActiveSheet.Paste

"Alan" wrote:

Massi,

The macro simply looks up the positions of the start row for your sort
and the end row. As it stands it will appear to do nothing but if you
insert a temporary line into the code

msgbox "Start Row = " & StartRow & vbcr & "End Row = " & EndRow

you will be able to see what is happening.

(Insert new code below the line that says "Now use StartRow and EndRow
to sort your data" you will see what it has calculated)

You can now use the start and end references to direct your sort.

Alan

massi wrote:
sorry but i cannot figure out how it works.
if i try to run the macro as normal nothing happens...

"Alan" wrote:


Sub SortData()

Dim StartDate As Date
Dim EndDate As Date
Dim StartRow As Integer
Dim EndRow As Integer
Dim i As Integer

' Allocate cells in which you can specify the
' StartDate and EndDate for your filter (Assumed D1 and D2 here)
' Enter dates into your chosen cells formatted as dates.

StartDate = Range("D1").Value
EndDate = Range("D2").Value

Range("A1").Select
' Find the StartDate position
Do Until ActiveCell.Offset(i, 0).Value = StartDate
i = i + 1
Loop

StartRow = Range("A1").Offset(i, 0).Row

' Reset the counter
i=0
' Find the EndDate position
Do Until ActiveCell.Offset(i, 0).Value = EndDate
i = i + 1
Loop

EndRow = Range("A1").Offset(i, 0).Row

' Now use StartRow and EndRow to sort your data

End Sub

massi wrote:
i have column A with the dates from 1 jan 06 to 31 dec 06 and the column next
to it has some data.
how can i do a filter that can sort the data only for the range i want (ie
from 1 jan to 22 apr 06)
thanks

Massi

Replace
Rows("StartRow:EndRow").Select
with
Rows(StartRow & ":" & EndRow).Select

Alan

massi wrote:
Hi Alan,
I have tried to put this after the code you gave me but it doesn't work. I
have also tried to put
StartDate instead StartRow but the result is the same...

Rows("StartRow:EndRow").Select
Selection.Copy
Sheets("Sheet3").Select
Sheets.Add
ActiveSheet.Paste

"Alan" wrote:

Massi,

The macro simply looks up the positions of the start row for your sort
and the end row. As it stands it will appear to do nothing but if you
insert a temporary line into the code

msgbox "Start Row = " & StartRow & vbcr & "End Row = " & EndRow

you will be able to see what is happening.

(Insert new code below the line that says "Now use StartRow and EndRow
to sort your data" you will see what it has calculated)

You can now use the start and end references to direct your sort.

Alan

massi wrote:
sorry but i cannot figure out how it works.
if i try to run the macro as normal nothing happens...

"Alan" wrote:


Sub SortData()

Dim StartDate As Date
Dim EndDate As Date
Dim StartRow As Integer
Dim EndRow As Integer
Dim i As Integer

' Allocate cells in which you can specify the
' StartDate and EndDate for your filter (Assumed D1 and D2 here)
' Enter dates into your chosen cells formatted as dates.

StartDate = Range("D1").Value
EndDate = Range("D2").Value

Range("A1").Select
' Find the StartDate position
Do Until ActiveCell.Offset(i, 0).Value = StartDate
i = i + 1
Loop

StartRow = Range("A1").Offset(i, 0).Row

' Reset the counter
i=0
' Find the EndDate position
Do Until ActiveCell.Offset(i, 0).Value = EndDate
i = i + 1
Loop

EndRow = Range("A1").Offset(i, 0).Row

' Now use StartRow and EndRow to sort your data

End Sub

massi wrote:
i have column A with the dates from 1 jan 06 to 31 dec 06 and the column next
to it has some data.
how can i do a filter that can sort the data only for the range i want (ie
from 1 jan to 22 apr 06)
thanks