Delete current month using AUTOFILTER

[Mslady]

Hi all,

Please can anyone help me with this?

1) I am looking put additional functionality in my current code (below)
to delete rows if value in column A (which is a date in this format
12/31/2005) is the current month.
i.e. if this month is November 2005, delete every row for november
2005-the current month.
How do i add to this code to do that?

2) Also, how do i add to this code to delete duplicate rows in my
sheet? Like if i have two exact dates in column A, delete one and keep
one!

Thanks in advance


Code:
--------------------

Worksheets("DataTable").Select
With ActiveSheet
If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter
.Range("A1").AutoFilter Field:=1, Criteria1:=#3/25/2005#, _
Operator:=xlOr, Criteria2:=#12/31/2004#
.Range("A1").CurrentRegion.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With

 

[Mslady]

Any help or ideas would be great. I particularly need number 1) done,
that takes priority for now
Thanks guys!

 

[Don Guillett]

try
Sub fileroutthismonth()
With Worksheets("sheet10")
startday = DateSerial(Year(Date), Month(Date), 1)
stopday = DateSerial(Year(Date), Month(Date) + 1, 1)
If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter
.Range("A1").AutoFilter Field:=1, Criteria1:=">=" & startday & "", _
Operator:=xlOr, Criteria2:=" & stopday & """
.Range("A1").CurrentRegion.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With
End Sub

 

[Ron de Bruin]

Hi

You can also use EasyFilter to do this
http://www.rondebruin.nl/easyfilter.htm

 

[Don Guillett]

actually this would be better.

Sub fileroutthismonth()
With Worksheets("sheet10")
startday = DateSerial(Year(Date), Month(Date), 1)
stopday = DateSerial(Year(Date), Month(Date) + 1, 1)
If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter
.Range("A1").AutoFilter Field:=1, Criteria1:=">=" & startday & "", _
Operator:=xlAnd, Criteria2:="<" & stopday & ""
.Range("A1").CurrentRegion.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

try
Sub fileroutthismonth()
With Worksheets("sheet10")
startday = DateSerial(Year(Date), Month(Date), 1)
stopday = DateSerial(Year(Date), Month(Date) + 1, 1)
If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter
.Range("A1").AutoFilter Field:=1, Criteria1:=">=" & startday & "", _
Operator:=xlOr, Criteria2:=" & stopday & """
.Range("A1").CurrentRegion.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With
End Sub

 

[Mslady]

Brilliant!!!
thanks Don! this works perfectly.


Code
-------------------
With Worksheets("DataTable")
startday = DateSerial(Year(Date), Month(Date), 1)
stopday = DateSerial(Year(Date), Month(Date) + 1, 1)
If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter
.Range("A1").AutoFilter Field:=1, Criteria1:=">=" & startday & "", _
Operator:=xlAnd, Criteria2:="<" & stopday & ""
.Range("A1").CurrentRegion.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With
-------------------


Thanks also Ron, for that resource. i have it bookmarked, im sure i
will come in handy.

Thanks guys! :cool

 

[Don Guillett]

Aw shucks, glad to help.