Code to merge set of rows

[KORKIS2]

I have code to search down a column for a range of rows now i need code to take that range and merge it all togeher in

 

[KORKIS2]

Ok thats the right concept it organizes the whole row based on the alphabetical order based on Column A
The only thing I need different it for it to alphabetically organize based off the rows ranges that are pulled up from the array that is found and stored on the right. This is very close
Thank you for your help



This is what I got

51.0125 Body 108.00 108.0 150512 851.0125 7 8 60.0125 Body 82.00 82.0 150512 860.0125 9 13 60.0125 Body 83.00 102.0 150512 868.9875 14 16 60.0125 Body 103.00 103.0 150512 60.0125 Body 109.00 110.0 150512 68.9875 Body 106.00 107.0 150512 51.0125 Head 105.00 105.0 150512 60.0125 Head 104.00 104.0 150512 68.9875 Head 111.00 111.0 150512 <colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody> </tbody>

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[KORKIS2]

Ok this is very close it is sorting based on alphabetical order and using column B while moving the whole row.
However It still needs to pull from the Array bounds on the right to get the correct rows otherwise its sorts down the whole column instead of just the individual row ranges.

This is what I got from Mick Gs Code it is very close once again thank you for your time.

51.0125	Body			108.00	108.0	150512				851.0125	7	8
60.0125	Body			82.00	82.0	150512				860.0125	9	13
60.0125	Body			83.00	102.0	150512				868.9875	14	16
60.0125	Body			103.00	103.0	150512
60.0125	Body			109.00	110.0	150512
68.9875	Body			106.00	107.0	150512
51.0125	Head			105.00	105.0	150512
60.0125	Head			104.00	104.0	150512
68.9875	Head			111.00	111.0	150512

<tbody>
</tbody>

This is what I'm looking for the final product to look like.

										851.013	7	8
										860.013	9	13
51.0125	Body			105	105	150512				868.988	14	16
51.0125	Head			108	108	150512
60.0125	Body			82	82	150512
60.0125	Body			83	102	150512
60.0125	Head			103	103	150512
60.0125	Head			104	104	150512
60.0125	Head			109	110	150512
68.9875	Body			106	107	150512
68.9875	Head			111	111	150512

<tbody>
</tbody>

 

[MickG]

Try this:-
NB:- The number of Bodies & Heads in your previous 2 List are not the same.???

[COLOR=Navy]Sub[/COLOR] MG16Jun20
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] K [COLOR=Navy]As[/COLOR] Variant
[COLOR=Navy]Dim[/COLOR] Dic [COLOR=Navy]As[/COLOR] Object
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A7"), Range("A" & Rows.Count).End(xlUp))
 Rng.Resize(, 7).Sort Rng(1)
  [COLOR=Navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
   Dic.CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] Not Dn.Value = "" [COLOR=Navy]Then[/COLOR]
        [COLOR=Navy]If[/COLOR] Not Dic.Exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
            Dic.Add Dn.Value, Dn
        [COLOR=Navy]Else[/COLOR]
            [COLOR=Navy]Set[/COLOR] Dic.Item(Dn.Value) = Union(Dic.Item(Dn.Value), Dn)
        [COLOR=Navy]End[/COLOR] If
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] K [COLOR=Navy]In[/COLOR] Dic.keys
    Dic.Item(K).Offset(, 1).Sort Dic.Item(K).Offset(, 1)(1), xlAscending
[COLOR=Navy]Next[/COLOR] K


[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[KORKIS2]

It changes everytime and so does the range at which the rows for the body and head are in changes to. That's why I used an Array to search and print them out on the sheet to find the ranges to sort the head and body in.

 

[KORKIS2]

Awesome man Thank you very much I appreciate your time.

 

[MickG]

Pleased its working for you.
Mick