Hi Howard,

Am Fri, 21 Feb 2014 00:02:18 -0800 (PST) schrieb L. Howard:

The first macro works okay except it takes about 4 to 5 seconds to run on 2000 +/- rows.

I'm wanting to adapt Garry's array code, below the first, and take a named range do my little For Each statement to each cell/element. I plugged in the range naming line, hope it is correct.

I presume this will be much faster than the loop I wrote.

column B is empty? Then try:

Sub MyScan2()
Dim LRow As Long
Dim myArr As Variant
Dim i As Long

LRow = Cells(Rows.Count, 1).End(xlUp).Row
myArr = Range("A2:B" & LRow)
For i = LBound(myArr) To UBound(myArr)
If Left(myArr(i, 1), 1) < "P" Then
myArr(i - 1, 2) = myArr(i, 1)
myArr(i, 1) = ""
End If
Next
Range("A2:B" & LRow).ClearContents
Range("A2:B" & UBound(myArr)) = myArr
Range("A2:A" & LRow).SpecialCells(xlCellTypeBlanks) _
.EntireRow.Delete
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2