Date Filtering with VBA

[robfo0]

Hi everyone,

I have a worksheet with a few columns of data, the first of which is a column of dates formatted as "mm/dd/yy". I have created a user form so that a user can input a date range, and a macro will delete any rows not in the specified range.

I have tried to start this by just making a macro to delete all the days which come before a specified range. The macro does delete some rows, but not all the ones with dates that are before the specified date. Here is the code I am using:

Sheets("Rows").Activate
For Each cell In Sheets("Rows").Range("A1", Range("A65536").End(xlUp))
If cell.Value < DATE_START.Value Then
cell.EntireRow.Delete
End If
Next

DATE_START is the value of the textbox in which i am inputting the date as "mm/dd/yy".

can someone tell me why this isnt working?

Thanks

[mdfh]

Your loop needs to go backwards from the last cell to the first.

[Mudface]

On 2002-05-11 13:38, mdfh wrote:
Your loop needs to go backwards from the last cell to the first.

Yer man is correct, take a look at Dave Hawley's OzGrid website or type in delete rows into the search engine on this site for some examples.

[robfo0]

Thanks guys, i found a solution to fix it with a search:


For iRow = Sheets("Rows").UsedRange.Rows.Count To 1 Step -1
If Cells(iRow, 1) < DATE_STARTvar Then Rows(iRow).Delete
Next iRow


But just being curious, how would i fix my original code to make it run properly? I tried changing the loop to:

For Each cell In Sheets("Rows").Range(Range("A65536").End(xlUp),"A1")

but that didnt work. Thanks

[Mudface]

Not really sure if you can- the For... Each... loop just goes from the first to last elements in the group and doesn't take a Step arguement, as the group could be just about anything.

[mdfh]

Try this :-

Dim rws#, iRow#
rws = [A65536].End(xlUp).Row
For iRow = rws To 1 Step -1
If Cells(iRow, 1).Value < DATE_START.Value Then
Rows(iRow).Delete
End If
Next


Alternatively, you can loop forwards from A1 to the last row with :-

Dim x%, toDelete As Range
For Each cell In Sheets("Rows").Range("A1", Range("A65536").End(xlUp))
If cell.Value < DATE_START.Value Then
If x = 1 Then
Set toDelete = Union(toDelete, cell)
Else
Set toDelete = cell
x = 1
End If
End If
Next
toDelete.EntireRow.Delete



[ This Message was edited by: mdfh on 2002-05-11 17:05 ]