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!