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 :)


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

Delete current month using AUTOFILTER
 

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

Delete current month using AUTOFILTER
 

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

"Mslady" wrote in
message ...

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

Delete current month using AUTOFILTER
 

Hi

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


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


"Mslady" wrote in message
...

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

Delete current month using AUTOFILTER
 

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

"Don Guillett" wrote in message
...
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

"Mslady" wrote in
message ...

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

Delete current month using AUTOFILTER
 

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

--
Mslad
-----------------------------------------------------------------------
Mslady's Profile: http://www.excelforum.com/member.php...fo&userid=2777
View this thread: http://www.excelforum.com/showthread.php?threadid=48190

Delete current month using AUTOFILTER
 

Aw shucks, glad to help.

--
Don Guillett
SalesAid Software

"Mslady" wrote in
message ...

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! :cool:


--
Mslady
------------------------------------------------------------------------
Mslady's Profile:
http://www.excelforum.com/member.php...o&userid=27776
View this thread: http://www.excelforum.com/showthread...hreadid=481904