On Tue, 6 Dec 2011 16:27:29 -0800 (PST), James Ravenswood
wrote:

On Dec 6, 4: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

As coded, you are paying a very tiny penalty in code execution time.
You don't need to Select in the loops. You can just loop over the
ranges.


Thanks for that James. I am running the live code as done, but am
trying to learn vba a bit better now.