Thanks Don,

' not quite sure what you want here.
If c.offset(,-1)<ShipmentDate_StartValue or _
c.offset(,-1)shipmentDate_EndValue) then rows(c.row).delete
Didn't explain very clearly here, the start date and end date are
named values with date & time format.
Column P has date & time showing, and the formula deletes the dates
not inbetween these 2 values (deletes the whole table row A to P -
shiftup)

I got your formula working by adapting it to column P
Sub foreachC()
Sheets("Dollies - Shipment").Activate
For Each c In Cells(2, "p").Resize(Cells(Rows.Count,
"P").End(xlUp).Row)
If c.Offset(, 0) < ShipmentDate_StartValue Or c.Offset(, 0)
shipmentDate_EndValue Then Rows(c.Row).Delete
Next c

The problem is I can only delete columns A to Q, as there is data in R
to T, and your formula deletes the whole row. Can this be changed


On Wed, 7 Dec 2011 06:02:39 -0800 (PST), Don Guillett
wrote:

I would not use the apply formula and then delete due to formula.
something like

Sub foreachC()
For Each c In Cells(2, "q").Resize(Cells(Rows.Count,
"Q").End(xlUp).Row)
' not quite sure what you want here.
If c.offset(,-1)<ShipmentDate_StartValue or _
c.offset(,-1)shipmentDate_EndValue) then rows(c.row).delete
Next c
End Sub
'==========
On Dec 6, 3:56*pm, vom wrote:
Hi all,

I am not very experienced in writing vba.
I have got the following code, put together by recording macros
mostly, and editing afterwards.

Is there a simpler way to construct the below code.

* * * * Range("Q2").Select
* * * * Do While Not IsEmpty(ActiveCell.Offset(0, -1))
* * * * ActiveCell.FormulaR1C1 = _

"=IF(OR('Pallet'!RC[-1]<ShipmentDate_StartValue,'Pallet'!RC[-1]ShipmentDat e_EndValue),""Del"",""Keep"")"
* * * * ActiveCell.Offset(1, 0).Select
* * * * *Loop
* * * * Range("Q2").Select
* Do While Not IsEmpty(ActiveCell.Offset(0, -1))
* * * * * * Do While ActiveCell = "Del"
* * * * * * ActiveCell.Offset(0, -16).Range("A1:Q1").Select
* * * * * * Selection.Delete Shift:=xlUp
* * * * * * ActiveCell.Offset(0, 16).Select
* * * * * * Loop
* * ActiveCell.Offset(1, 0).Select
* * Loop
* * * * ActiveSheet.Range("Q:Q").ClearContents

Data resides in column a to p
formula above check the data, populates column q, and acts on it
depending on value.

Cannot delete the whole row as data is on further columns (unable to
change this)

Thanks in advance
edul