Sorting data using merged cells as keys

[Sinobato]

Hi,

I'm not sure if somebody has already posted this here, but my initial
searches failed me. So, after trying to solve this problem for several
days now (since I don't know much about VBA), I would like to ask if
someone from the group can recommend a solution to my problem:

If I have a worksheet, where rows 5 to 7 contains merged cells, ie.
D5:E5, F5:G5, D6:E6, etc., and the other cells below these rows are
all single cells with values. What I would like to do is to sort all
these data, by using one of the rows, ie. row 7 as keys, and then
after sorting this, the original values under these columns will
follow where the new column of the merged cell will be.

Example: (Before)

A B C D E F G
1 Some text here
2 Some text here
3 Some text here
4 Blank row
5 Head1 Head2
6 Head3 Head4
7 A B
8 data1 data2 data3 data4
9 data5 data6 data7 data8
10

When I sort from D5 to G10, using row 7 as my sort keys, what I would
like to happen is this:

A B C D E F G
1 Some text here
2 Some text here
3 Some text here
4 Blank row
5 Head1 Head2
6 Head3 Head4
7 A B
8 data3 data4 data1 data2
9 data7 data8 data5 data6
10

What I had in mind was to create a temporary worksheet, copy the rows
used as sort key to the new worksheet, sort the data on the new
worksheet, then try to determine the old location of data before (ie.
where was A before (F7:G7)), then copy the data from F5:G10 under the
new temporary sheet, and once all data are copied to the temporary
sheet in the new order, copy the whole data back to the original
worksheet. Is there any way easier than this?

Firstly, I needed to know the last column and last row so that I can
copy the appropriate data to the temporary worksheet and be able to
copy these data back to the correct row/column after sorting. With
this alone, I'm already out of ideas on how to implement this on VBA!
Any ideas?

Any advice will be highly appreciated!

Thanks,
Sinobato

 

[Tom Ogilvy]

Sub Tester1()
Dim rng As Range
With Worksheets("Data")
Set rng = .Range("D5").CurrentRegion
End With
MsgBox rng.Address(external:=True)
rng.Copy
With Worksheets("Temp")
.Range("D5").PasteSpecial xlValues
End With


End Sub