Okay,

I've tried the below, and it is working except:

For some reason it deletes the header from B1 (this is easily solved though
by setting the range as B2:B & Last Row).

Also, I'm using the following piece of code for the input (G4)

Sheets("Lookup").Range("G4").Value = InputBox("Please enter the end of
Period (dd/mm/yy)", "Report Year")

Now I think when it's populating the box, it's doing so as a string rather
than a number - so the macro doesn't work (although funnily enough that
reference does work as a date in a formula I'm also using). If I just type
in a date (say into G5) and then reference that, the macro understands it
perfectly.

So I guess I need to figure out how to tweak my input so it's returned as a
true date rather than a string? I tried just recording macro which would
simply change G4 to text to columns prior to running your code (messy yes,
but I thought it would work) but it doesn't seem to...

"Mike H" wrote:

Hi,

Try this

Sub sth()
Dim LastRow as long
lastrow = Sheets("Data").Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set MyRange = Sheets("Data").Range("B1:B" & lastrow)
For Each c In MyRange
If c.Value Sheets("lookup").Range("G4") Then
c.ClearContents
End If
Next

Mike

"bawpie" wrote:

Okay, hopefully this is the last question today!

I've found the below formula in one of the posts here, but my brain has
completely failed on me and I'm not sure how to adapt it to my requirements.

I have a value which is entered by the user after a prompt, that populates
in cell G4 in a sheet called 'Lookup'. For arguments sake, the current value
is 30/11/09.

I have another worksheet, 'Data', which has a column populated with dates
(say column B). I'd like a macro that looks in column B on the data sheet,
and clears out any dates which are greater than the value in cell G4. I just
want to empty those cells, not delete columns or anything else.

Sub sth()
Dim cell As Range

For Each cell In Selection
If cell.Value < 1000 Then
cell.ClearContents
End If
Next cell


End Sub