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.

Thanks.
Howard

The data starts out like this all in column A, where the ABCDEnum 'belongs' to the P-num just above it. Lots of P-num's values have no ABCDEnum's and then are ignored.

P-4352
P-3534
P-4568
ABCDE123
P-4568
ABCDE124
P-4568
ABCDE125

And ends up like this in column A and B

P-4352
P-3534
P-4568 ABCDE123
P-3534
P-4568 ABCDE124
P-3534
P-4568 ABCDE125


Sub MyScan()

Dim lr As Long
Dim c As Range
Dim Rscan As Range

Application.ScreenUpdating = False

lr = Cells(Rows.Count, 1).End(xlUp).row
Set Rscan = Range("A2:A" & lr)

For Each c In Rscan
If Left(c, 1) < "P" Then
c.Cut c.Offset(-1, 1)
End If
Next

Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete
Application.ScreenUpdating = True
End Sub



Sub GarryScan()
Dim v, vaMyVals(), iIncr%, RngRefs As Range

Range("A2").Select
Set RngRefs = Range(ActiveCell.Address, ActiveCell.Offset.End(xlDown).Address)

For Each v In Split(Range("Rngrefs").Value, ",")
ReDim Preserve vaMyVals(iIncr)
vaMyVals(iIncr) = Range(v).Value
iIncr = iIncr + 1
Next 'v
'Dump the array into the target sheet
'...
End Sub