Delete current month using AUTOFILTER

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

Please Login or Register  to view this content.

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

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

--
Don Guillett
SalesAid Software
[email protected]
"Mslady" <[email protected]> wrote in
message news:[email protected]...
>
> 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
> ------------------------------------------------------------------------
> Mslady's Profile:
http://www.excelforum.com/member.php...o&userid=27776
> View this thread: http://www.excelforum.com/showthread...hreadid=481904
>

Hi

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Mslady" <[email protected]> wrote in message
news:[email protected]...
>
> Any help or ideas would be great. I particularly need number 1) done,
> that takes priority for now
> Thanks guys!
>
>
> --
> Mslady
> ------------------------------------------------------------------------
> Mslady's Profile: http://www.excelforum.com/member.php...o&userid=27776
> View this thread: http://www.excelforum.com/showthread...hreadid=481904
>

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
[email protected]
"Don Guillett" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Mslady" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> > ------------------------------------------------------------------------
> > Mslady's Profile:
> http://www.excelforum.com/member.php...o&userid=27776
> > View this thread:
http://www.excelforum.com/showthread...hreadid=481904
> >
>
>

Brilliant!!!
thanks Don! this works perfectly.

Please Login or Register  to view this content.

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

Thanks guys!

Aw shucks, glad to help.

--
Don Guillett
SalesAid Software
[email protected]
"Mslady" <[email protected]> wrote in
message news:[email protected]...
>
> 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 it
> will come in handy.
>
> Thanks guys!
>
>
> --
> Mslady
> ------------------------------------------------------------------------
> Mslady's Profile:
http://www.excelforum.com/member.php...o&userid=27776
> View this thread: http://www.excelforum.com/showthread...hreadid=481904
>