Your Format Function returns a string version of your date, so what you are
attempting to do is compare a string data type to a date/time data type,
which this doesn't work out. Maybe you could try the following before you
get to the start of your Select Case series.

Dim Date1 as Date
Date1 = Date

Or better yet, sense the Date Function already contains the current date
(Based on your PC date/time), why don't you just skip the variable all
together and just compare the cell to the Date Function.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Colin" wrote in message
m...
This is going to be so simple compared to some of the posts I've read
trying to track down an answer to my problem but I would be very
grateful for a solution. I've actually achieved a lot with coding for
all sorts of purposes but with no programming background there are
some simple things that just defeat me.

I?m trying to use VBA to delete any rows in a worksheet range which
have a date in the future, while ignoring rows where the target cell
contains other values ? in this case AA, REV, a date in the past, or
is blank.

The problematic section of code is as follows:

Date1 = Format(Now, "dd/mm/yy")
'MsgBox "Date1: " & Date1
Do Until ActiveCell.Value = ""
Select Case ActiveCell.Offset(0, 1).Value
Case "AA", "REV", "", Is < Date1
Case Is Date1
ActiveCell.EntireRow.Delete Shift:=xlUp
End Select
ActiveCell.Offset(1, 0).Select
Loop

It does what I expect for cells with AA, REV, Blank and a date in the
past i.e. nothing, but cells with a date in the future get picked as <
Date1 when they are greater. (I separated out Case Is <Date1 at one
point to try and work out what was going on). In my despair, I checked
the values of Date1 and my target cell using MsgBox and both values
are identical but the Case expression Is Date1 just doesn?t work and
I don?t know why.

I tried having a cell value of 11/09/03 i.e. equal to Date1 and using
the code Case Is = Date1 to see if that would give me any clues but it
just skips over this as well and doesn?t pick it up.

I tried Case Date1 and then Case Else, but AA, REV etc get picked up
as Date1.

Some/most/all of you guys will find this embarrassingly easy to solve
and no sort of challenge to your skills but it?s driving me insane and
I would be really grateful for your help.

Thanks.

Colin

Thank you Ronald!

I knew the answer would be clear to someone who knew what they were
doing! I've recoded as you suggested just using Date and it works - all
I needed to do then was to code backing up a row after the Delete so as
not to miss any rows i.e.

Case Is Date
ActiveCell.EntireRow.delete
ActiveCell.Offset(-1, 0).Select
End Select
ActiveCell.Offset(1, 0).Select

Many thanks!

Using a newsgroup is a new one for me as well and I'm more than
impressed! I work as a psychologist in the National Health Service and
have developed stuff in Excel for processing referrals, activity data
etc becasue there's no-one in the Trust's IT department who will/can do
it. i.e. no-one I can ask anything about VBA and I have to do most of
the coding in my own time.

I'll have to write up an issue I've got using Advanced Filter, which
includes absolutley everything in the results when I have a blank cell
as part of my criteria - I actually need to filter for blank cells - and
see if someone can help with that!

Colin


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

"Colin Elliott" wrote in message
...

I'll have to write up an issue I've got using Advanced Filter, which
includes absolutley everything in the results when I have a blank cell
as part of my criteria - I actually need to filter for blank cells - and
see if someone can help with that!

If you want your filtered data to only show rows where there is a blank cell
in column A, in the cell containing the criteria for column A, type an equal
sign (=).

--
Dianne

Your method with the Do Until...Loop works too with the help of the Select
Method and offsetting as far as moving from row to row. However, given the
issues that I have had in the past, in most cases, I try to avoid using 3
things, Active<Object (I.e. ActiveCell, ActiveWorksheet), Select Method (On
Range), and Activate Method (On Worksheet or Workbook). That's cause there
could be times when the code could be refering to something else that you
didn't want it to refer to, if you aren't careful about the use of these
things.

Here's a demo type code that I have used.

Sub RecursiveLoopsThroughCells()
Dim I as Long, WS as Worksheet, LastRow as Long
Set WS = Thisworkbook.Worksheet("Sheet1")
LastRow = WS.Range("C65536").End(xlUp).Row
For I = 4 to LastRow
Select Case ws.Range("C" & I).Value
Case "AA", "REV", "", Is < Date
Case Is Date
WS.Range("C" & I).EntireRow.Delete Shift:=xlUp
I = I - 1
LastRow = LastRow - 1
Case Else
End Select
Next I
End Sub

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Colin Elliott" wrote in message
...
Thank you Ronald!

I knew the answer would be clear to someone who knew what they were
doing! I've recoded as you suggested just using Date and it works - all
I needed to do then was to code backing up a row after the Delete so as
not to miss any rows i.e.

Case Is Date
ActiveCell.EntireRow.delete
ActiveCell.Offset(-1, 0).Select
End Select
ActiveCell.Offset(1, 0).Select

Many thanks!

Using a newsgroup is a new one for me as well and I'm more than
impressed! I work as a psychologist in the National Health Service and
have developed stuff in Excel for processing referrals, activity data
etc becasue there's no-one in the Trust's IT department who will/can do
it. i.e. no-one I can ask anything about VBA and I have to do most of
the coding in my own time.

I'll have to write up an issue I've got using Advanced Filter, which
includes absolutley everything in the results when I have a blank cell
as part of my criteria - I actually need to filter for blank cells - and
see if someone can help with that!

Colin


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!