Hi, I am relatively new to VBA in excel and am having a hard time figuring out how to seach a column for a value and select and copy the range from the first to last match. And move that to column E.

For ex.

Column A B C D E F
Row
1 1 5 1
2 1 6 3
3 1 7
4 2 6
5 2 3
6 2 5
7 3 2
8 3 4
9 4 1
10

I want to search column A Range 1: until blank (in this case row 10). For the value in "D1". When it finds the first match i want to select that cell and each cell until the value no longer matches. (Column A will always be grouped together - all the 1's in line the 2's in line the 3's etc.)

So it would grab "1" the value in "D1" and it would select A1:A3.
Then Selection.End(xlToRight) so that A1:B3 is now selected. Cut that information and move it to cell E1.

Then repeat the process for "D2". (still searching column A until Row 10, even though rows 1, 2 and 3 are now blank) Pasting the information below the information that was pasted prior.

And continue until it reaches a blank in Column D.

The output would be

Column A B C D E F
Row
1 1 1 5
2 3 1 6
3 1 7
4 2 6 3 2
5 2 3 3 4
6 2 5
7
8
9 4 1
10


If there is a way to do this it would be greatly helpful. I have been able to make some things work but have been having a tough time. Thanks for the help


I attached two pictures of the examples above so it was readable.

Here is another way of describing what I am trying to do.

Select Cell "D1" if blank end sub
Set Value of "D1" as search variable
Set range to search as (A1:To first blank)
Search range for first match
Select cell
Serach range for last match
Select Cell

Select the range between the first and last match (including matches)
xl.totheright

Cut data and move to Column E

Repeat by select Cell "D2" if blank end sub
active Cell value as search variable

Range Same as above (Not until blank but rather still, A1:A11 [which is what the first blank would have been prior to cutting the amounts from Column A1])


Repeat for D3 and so on until blank