Sort list in table then seperate

[nparsons75]

Hi, is it possible, with a macro, to sort a list in a table and then seperate the list by data in one column.

For example,

MACHINE	ITEM	QTY	DATE
001	CAR	5	16/7/17
002	BUS	1	16/7/17
009	PLANE	2	16/7/17
004	BOAT	5	16/7/17
001	SHIP	7	16/7/17
002	BIKE	3	16/7/17
009	CYCLE	1	16/7/17
004	PLANE	1	16/7/17
006	BIKE	9	16/7/17
004	SHIP	3	16/7/17
006	CAR	4	16/7/17

<tbody>
</tbody>

Taking the data above I would like a button to run a macro that will sort the data by column A and then seperate the list by a dividing row between groups. See example below.

In between each of the grouped items I would need a row that has a colour fill to seperate them visually. Would this even be possible? Thanks in advance.

1 CAR 5 16/07/2017 1 SHIP 7 16/07/2017 2 BUS 1 16/07/2017 2 BIKE 3 16/07/2017 4 BOAT 5 16/07/2017 4 PLANE 1 16/07/2017 4 SHIP 3 16/07/2017 6 BIKE 9 16/07/2017 6 CAR 4 16/07/2017 9 PLANE 2 16/07/2017 9 CYCLE 1 16/07/2017 <colgroup><col width="80" style="width:60pt" span="4"> </colgroup><tbody> </tbody>

<colgroup><col width="80" style="width:60pt" span="4"> </colgroup><tbody>
</tbody>

 

[Akuini]

Hi, is it possible, with a macro, to sort a list in a table and then seperate the list by data in one column.

Try this:

Sub a1014729a()
Dim rng As Range
Dim i As Long, ra As Long

ra = Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
Set rng = Range(Cells(1, "A"), Cells(ra, "D"))
    rng.Sort key1:=Range("A1"), order1:=xlAscending, _
        Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

For i = ra To 3 Step -1
If Cells(i, "A").Value <> Cells(i - 1, "A").Value Then
Cells(i, "A").EntireRow.Insert
End If
Next

End Sub

 

[nparsons75]

Hi, apologies I would need some more info, where to put it etc. My table actually has more columns than the example, do I just replace the A and the D in your code?

Thanks for your help.

 

[Akuini]

Hi, apologies I would need some more info, where to put it etc. My table actually has more columns than the example, do I just replace the A and the D in your code?

Thanks for your help.

Yes, if you have data until column H, then:

Set rng = Range(Cells(1, "A"), Cells(ra, "H"))

This is from: http://www.contextures.com/xlvba01.html
Copy Excel VBA Code to a Regular Module
Instead of starting from scratch, if you need an Excel macro, you can often find sample code at reputable sites on the internet. To copy that code, and add it to one of your workbooks, follow these steps:

• Copy the sample code that you want to use
• Open the workbook in which you want to add the code
• Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
• Choose Insert | Module
• Where the cursor is flashing, choose Edit | Paste