One way:

For the delete code (note that for DateSerial the "zeroth" day of the
month is the last day of the previous month):

Dim dtTemp As Double
dtTemp = ActiveSheet.Range("A4").Value
ActiveSheet.Rows(4).Resize(Day(DateSerial( _
Year(dtTemp), Month(dtTemp) + 1, 0))).Delete



In article ,
"Robert Hargreaves" wrote:

I have changed my code to the same as at the bottom of the page but have 2
issue with the code. I cant specify * as a year for all date find criteria
as the code doesn't allow for this. How could I change the code to allow
for all the 2005's to be replaced with a wildcard.

I also need to add a month to the end of the sheet but depending on which
month it is that is removed there will be a different range. I have written
some code to find the first empty cell which works but arent sure how to
modify a line to use activecell as the variable for adding a new month.

Heres the code

The code for delete is like this.

If ActiveSheet.Range("$A$4").Value = #1/1/2005# Then
ActiveSheet.Rows("4:34").Delete
ElseIf ActiveSheet.Range("$A$4").Value = #2/1/2008# Then
ActiveSheet.Rows("4:32").Delete
ElseIf ActiveSheet.Range("$A$4").Value = #2/1/2012# Then
ActiveSheet.Rows("4:32").Delete
ElseIf ActiveSheet.Range("$A$4").Value = #2/1/2016# Then
ActiveSheet.Rows("4:32").Delete
ElseIf ActiveSheet.Range("$A$4") = #2/1/2005# Then
ActiveSheet.Rows("4:31").Delete
ElseIf ActiveSheet.Range("$A$4") = #3/1/2005# Then
ActiveSheet.Rows("4:34").Delete
ElseIf ActiveSheet.Range("$A$4") = #4/1/2005# Then
ActiveSheet.Rows("4:33").Delete
ElseIf ActiveSheet.Range("$A$4") = #5/1/2005# Then
ActiveSheet.Rows("4:34").Delete
ElseIf ActiveSheet.Range("$A$4") = #6/1/2005# Then
ActiveSheet.Rows("4:33").Delete
ElseIf ActiveSheet.Range("$A$4") = #7/1/2005# Then
ActiveSheet.Rows("4:34").Delete
ElseIf ActiveSheet.Range("$A$4") = #8/1/2005# Then
ActiveSheet.Rows("4:34").Delete
ElseIf ActiveSheet.Range("$A$4") = #9/1/2005# Then
ActiveSheet.Rows("4:33").Delete
ElseIf ActiveSheet.Range("$A$4") = #10/1/2005# Then
ActiveSheet.Rows("4:34").Delete
ElseIf ActiveSheet.Range("$A$4") = #11/1/2005# Then
ActiveSheet.Rows("4:33").Delete
ElseIf ActiveSheet.Range("$A$4") = #12/1/2005# Then
ActiveSheet.Rows("4:34").Delete
End If

The code for add would be something like this -

Range("A1").Select

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

'This is where I am stuck....

'Range("A5:A6").AutoFill Destination:=Range("A5:A9") - Recommended code to
autofill

'Range(ActiveCell:Activecell - 1).Autofill
Destination:=Range("ActiveCell:????) - code modified to use autofil as
variable which doesnt work.

End Sub

Thanks
Rob