if the order of the values is not important you can copy and transpose

and then sort column A in Sheet2 to eliminate the blank cells.



Or you can read the values in an array:


I'll post this last code in the sheet for reference. Thanks.

I think a better way to describe what goes to sheet 2 two is current need.

With the rows designated C3 and down as A, B, C, D, etc. So in the drop down in C1 the choices may be B, E, then F, moving only three rows (one at a time) to sheet 2 at this time. Then later may want G, & H. Or any other combination from time to time.

I suspect the data are part numbers which gets changed, added to and deleted.

I have been trying to make each transpose go to a column for each row copied and transposed. Next transpose goes to next empty column on sheet 2. That has become more difficult than I guessed.

Having seen several transposes in a single column, seems clumsy to analyze.

lCol = .Cells(R.Row, .Columns.Count).End(xlToLeft).Column

This is easy enough for sheet 1, but I am having major problems using like code to find the lastColumn for sheet 2 and using it in the copy-to code line.

Howard