delete rows in excel by service date

[jeff.white]

I have an excel sheet that contains about 300 rows of data. One
column, P, has the hire date. What I'm looking to do via vba is to
delete all rows where the hire date is not 3 months prior next month.
In other words, next month is June, so I want only those rows where
the hire date is the month of March (3 months prior to June)...I
normally use the filter commands which works OK, but I thought since
this is a monthly report I can speed things up and run it via VBA.

Any suggestions?

 

[ra]

Try this, it will allow you to select date range to delete.. (update
column # as appropriate -this assumes column 13)

Sub DeleteDatesRIM()
Dim StDate As Date, FinDate As Date, LastRow&, i&
StDate = InputBox("Dates to be Deleted- ENTER Start Date (DD/MM/
YYYY)")
FinDate = InputBox("Dates to be Deleted-ENTER End Date (DD/MM/
YYYY)")
LastRow = Cells(Rows.Count, 13).End(xlUp).Row
For i = LastRow To 2 Step -1
If Cells(i, 13).Value >= StDate And _
Cells(i, 13).Value <= FinDate Then
Rows(i).Delete
End If
Next i
End Sub

 

[Guest]

Turn on the macro recorder while you apply the filter.

Turn off the macro recorder and use the recorded code as model code to build
your macro.

 

[Nigel]

One approach would be to scan the rows from last to first, checking if
column P has a valid date (will not remove titles etc.)
The code determines the last row from the data in column P. Adjustments are
made for dates that span a year rollover

Sub RemoveRows()
Dim xr As Long, xlr As Long, mth As Integer
Application.ScreenUpdating = False
with Sheets("Sheet1")
xlr = .Cells(.Rows.Count, "P").End(xlUp).Row
mth = Month(Date)
If mth < 3 Then mth = mth + 12
For xr = xlr To 1 Step -1
If IsDate(.Cells(xr, "P")) Then
If mth <> Month(.Cells(xr, "P")) + 2 Then
.Rows(xr).EntireRow.Delete shift:=xlUp
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub

 

[jeff.white]

One approach would be to scan the rows from last to first, checking if
column P has a valid date (will not remove titles etc.)
The code determines the last row from the data in column P. Adjustments are
made for dates that span a year rollover

Sub RemoveRows()
Dim xr As Long, xlr As Long, mth As Integer
Application.ScreenUpdating = False
with Sheets("Sheet1")
xlr = .Cells(.Rows.Count, "P").End(xlUp).Row
mth = Month(Date)
If mth < 3 Then mth = mth + 12
For xr = xlr To 1 Step -1
If IsDate(.Cells(xr, "P")) Then
If mth <> Month(.Cells(xr, "P")) + 2 Then
.Rows(xr).EntireRow.Delete shift:=xlUp
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub

--
Cheers
Nigel







- Show quoted text -

Thanks to all that responded! I've was able to incorp the first
suggestion, I made a couple of modifications. Bascially I used stdate
is <= mm/dd/yy OR findate >= mm/dd/yy. This leaves the target month
in the sheet, deleting all those greater or less than....Thanks
again...