Do you want to cut matches and insert them offset(1,-1) and also
transfer the matches to a new sheet?
Or what data do you want to transfer? If all values from column A occure
in the range C1:HQ950 you could copy column A and paste only the values.
Then wraptext is false in the new list.
Are some cells merged in the range C1:HQ950? And in column A also?


Regards
Claus B.


Do you want to cut matches and insert them offset(1,-1) and also
transfer the matches to a new sheet?

That is exactly the aim. Probably should have said so to start with, but wasn't sure if I was even going to proceed with the first part.

Here is what I have so far, which seems to work for single row data, and the UNION part, I find does not copy two ranges, plus it is a loop which will take forever to run. But I thought I would give it a try.

Here is the transfer part I have, where the ElseIf part does not work.

If Not spNum Is Nothing Then
If spNum.Offset(1, 1) = "" Then
spNum.Resize(1, 23).Copy Sheets("New List").Range("A" & Rows.Count).End(xlUp)(2)

ElseIf spNum.Offset(1, 1) < "" Then

Set rng1 = spNum.Resize(1, 12)
Set rng2 = spNum.Offset(1, 1).Resize(1, 11)

Set rngUnion = Application.Union(rng1, rng2)
rngUnion.Copy Sheets("New List").Range("A" & Rows.Count).End(xlUp)(2)
End If
End If


Are some cells merged in the range C1:HQ950? And in column A also?

Many, many in the range, but not in column A. However the code you provided, dumps the merged cells.

I think I see what you are saying about the transfers. I'll study that some more.

Howard