Sub tester()


Dim keydate As Date
Dim yr As Long
Dim target As Range
Dim bremove As Boolean

'initialise date
keydate = DateSerial(Year(Range("B1").Value), Month(Range("B1").Value), 1)


Set target = Range("B2")
Do Until target.Value = ""
bremove = True
'check this month
If DateSerial(Year(target.Value), Month(target.Value), 1) = keydate Then
bremove = False
'check next month
ElseIf DateSerial(Year(target.Value), Month(target.Value) - 1, 1) =
keydate Then
bremove = False
End If
If bremove Then
Rows(target.Row).Delete
End If

Set target = target.ffset(1)
Loop

End Sub




"MSchmidty2" wrote in message
...
Hi,
I'm working on a chart which has a variety of job numbers in column a.
These job #'s are coupled with dates in column b. I need to check the
date
of the top cell in the column, and then keep the dates from the rest of
the
month from that date, and also the next month, while deleting the rest of
the
job #'s and dates beyond that. I can't hide the rows because there is
more
information that is needed. Any tips would be appreciated. Thanks!

Thanks for the reply and your help, Patrick. I'm a little rusty with VBA and
this code is definitely over my head. I receive a run-time error 13 'Type
Mismatch' when I attempt to run this macro, and I've tried to determine why,
but have been unsuccesful.

"Patrick Molloy" wrote:

Sub tester()


Dim keydate As Date
Dim yr As Long
Dim target As Range
Dim bremove As Boolean

'initialise date
keydate = DateSerial(Year(Range("B1").Value), Month(Range("B1").Value), 1)


Set target = Range("B2")
Do Until target.Value = ""
bremove = True
'check this month
If DateSerial(Year(target.Value), Month(target.Value), 1) = keydate Then
bremove = False
'check next month
ElseIf DateSerial(Year(target.Value), Month(target.Value) - 1, 1) =
keydate Then
bremove = False
End If
If bremove Then
Rows(target.Row).Delete
End If

Set target = target.ffset(1)
Loop

End Sub




"MSchmidty2" wrote in message
...
Hi,
I'm working on a chart which has a variety of job numbers in column a.
These job #'s are coupled with dates in column b. I need to check the
date
of the top cell in the column, and then keep the dates from the rest of
the
month from that date, and also the next month, while deleting the rest of
the
job #'s and dates beyond that. I can't hide the rows because there is
more
information that is needed. Any tips would be appreciated. Thanks!

I see a typo I'm afraid

line
Set target = target.ffset(1)
should be
Set target = target.Offset(1)

strange since its a cut/paste from my own test module. apologies. Use
debug/compile to find these

we need to get this month and next month from the date you have in cell B1
so we get the year & month from B1, which is the system date, and set the
day to 1
this is saved in keydate with this code:
keydate = DateSerial(Year(Range("B1").Value), Month(Range("B1").Value), 1)

keydate is a variable decalred as a DATE type
DATESERIAL(year,month,day) is a function for creating dates eg
DATESERIAL(2009,7,15)

then we set off using each cell from B2 and go down the column using a loop
Target is a variable declared as a range, an object, and we initially set it
to cell B2, before we end the loop, we move it down the column by setting it
to the next cell below using the OFFSET method
SET Target = Target.Offset(1)

now Targets date is set to the 1st of the month and we compare this to the
keydate and the month after the key date - we set to the first to avoid
issues with day counts
if the date in Target isn't one of these two dates, we don't want it, so we
can delete the row


"MSchmidty2" wrote in message
...
Thanks for the reply and your help, Patrick. I'm a little rusty with VBA
and
this code is definitely over my head. I receive a run-time error 13 'Type
Mismatch' when I attempt to run this macro, and I've tried to determine
why,
but have been unsuccesful.

"Patrick Molloy" wrote:

Sub tester()


Dim keydate As Date
Dim yr As Long
Dim target As Range
Dim bremove As Boolean

'initialise date
keydate = DateSerial(Year(Range("B1").Value), Month(Range("B1").Value),
1)


Set target = Range("B2")
Do Until target.Value = ""
bremove = True
'check this month
If DateSerial(Year(target.Value), Month(target.Value), 1) = keydate
Then
bremove = False
'check next month
ElseIf DateSerial(Year(target.Value), Month(target.Value) - 1, 1) =
keydate Then
bremove = False
End If
If bremove Then
Rows(target.Row).Delete
End If

Set target = target.ffset(1)
Loop

End Sub




"MSchmidty2" wrote in message
...
Hi,
I'm working on a chart which has a variety of job numbers in column a.
These job #'s are coupled with dates in column b. I need to check the
date
of the top cell in the column, and then keep the dates from the rest of
the
month from that date, and also the next month, while deleting the rest
of
the
job #'s and dates beyond that. I can't hide the rows because there is
more
information that is needed. Any tips would be appreciated. Thanks!

I actually spotted that typo and fixed it, but your explanation made it
easier to understand. However, I now receive a 'Run Time Error 424' Object
Required. I've tried to figure it out myself, but have been unable. Thanks
again for your help, Patrick.

"Patrick Molloy" wrote:

I see a typo I'm afraid

line
Set target = target.ffset(1)
should be
Set target = target.Offset(1)

strange since its a cut/paste from my own test module. apologies. Use
debug/compile to find these

we need to get this month and next month from the date you have in cell B1
so we get the year & month from B1, which is the system date, and set the
day to 1
this is saved in keydate with this code:
keydate = DateSerial(Year(Range("B1").Value), Month(Range("B1").Value), 1)

keydate is a variable decalred as a DATE type
DATESERIAL(year,month,day) is a function for creating dates eg
DATESERIAL(2009,7,15)

then we set off using each cell from B2 and go down the column using a loop
Target is a variable declared as a range, an object, and we initially set it
to cell B2, before we end the loop, we move it down the column by setting it
to the next cell below using the OFFSET method
SET Target = Target.Offset(1)

now Targets date is set to the 1st of the month and we compare this to the
keydate and the month after the key date - we set to the first to avoid
issues with day counts
if the date in Target isn't one of these two dates, we don't want it, so we
can delete the row


"MSchmidty2" wrote in message
...
Thanks for the reply and your help, Patrick. I'm a little rusty with VBA
and
this code is definitely over my head. I receive a run-time error 13 'Type
Mismatch' when I attempt to run this macro, and I've tried to determine
why,
but have been unsuccesful.

"Patrick Molloy" wrote:

Sub tester()


Dim keydate As Date
Dim yr As Long
Dim target As Range
Dim bremove As Boolean

'initialise date
keydate = DateSerial(Year(Range("B1").Value), Month(Range("B1").Value),
1)


Set target = Range("B2")
Do Until target.Value = ""
bremove = True
'check this month
If DateSerial(Year(target.Value), Month(target.Value), 1) = keydate
Then
bremove = False
'check next month
ElseIf DateSerial(Year(target.Value), Month(target.Value) - 1, 1) =
keydate Then
bremove = False
End If
If bremove Then
Rows(target.Row).Delete
End If

Set target = target.ffset(1)
Loop

End Sub




"MSchmidty2" wrote in message
...
Hi,
I'm working on a chart which has a variety of job numbers in column a.
These job #'s are coupled with dates in column b. I need to check the
date
of the top cell in the column, and then keep the dates from the rest of
the
month from that date, and also the next month, while deleting the rest
of
the
job #'s and dates beyond that. I can't hide the rows because there is
more
information that is needed. Any tips would be appreciated. Thanks!