Solved: Should be a simple sort, but is it?

[MRichmond]

This is probably a very simple request, but I can’t seem to figure it out because I don’t know enough about VBA.

I have a spreadsheet that has various blocks of data on a number of sheets that need to be sorted on two columns.

The problem I have is that although the data fields are always seven columns wide and sorted on the same columns, they are not in the same columns on each sheet, nor do they contain the same number of rows in each block.

In order to speed up the process I thought the easiest way would be to highlight each data block in turn and have a macro button that would sort the selected data area on column 7 descending and column 2 descending, which is exactly what I am doing manually using Data>Sort, so then at least I am only pressing one button instead of 5.

Any ideas?

[Bob Phillips]

Do the key columns always have the same header text? If so, the code could find the column and sort by the found column.

[MRichmond]

Although the column headers would be the same, the blocks of data are contained within the column, and only the first block has the headers. In the example sheet attached the four blocks of data would all be sorted seperately. I manually select area B2:H10 and sort on column H & D descending. In other sheets the data is always 7 columns wide, in the same order, but not necessarily starting in column b.

[Bob Phillips]

So you want to sort B2:H10, B12:H28, B30:H47 etc. and then the other sheets?

[MRichmond]

thats correct sir, although the number of rows would vary. This week it is B2:H10, but next week it may be B2:H8.

[Bob Phillips]

Understood.

I was thinking something like this



Sub SortData()
Dim StartCell As Range
Dim Lastrow As Long
Dim Grouplast As Long
Dim SortCol1 As Long
Dim SortCol2 As Long

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
SortCol1 = Application.Match("% In Branch", .Rows(1), 0)
SortCol2 = Application.Match("In Stock", .Rows(1), 0)

Set StartCell = .Range("B2")
Do

Grouplast = StartCell.End(xlDown).Row
StartCell.Resize(Grouplast - StartCell.Row + 1, 7).Sort _
key1:=StartCell.Offset(0, 6), order1:=xlDescending, _
key2:=StartCell.Offset(0, 2), order2:=xlDescending, _
Header:=xlNo
Set StartCell = StartCell.Offset(Grouplast - StartCell.Row + 2)
Loop While StartCell.Row <= Lastrow
End With
End Sub

[MRichmond]

Mr XLD, that works better than I expected. Last question, the code only works as expected if the start is B2, how would I alter the code so it would work if I started in D2 or F2

[Bob Phillips]

If you start at D2, does that mean you don't want to sort columns B & C?

[MRichmond]

That's correct, wherever I start, the data block will always be 7 columns wide by x rows , with the same headers and it's just these data blocks that need sorting

[Bob Phillips]

That's what I originally thought, and B:H is 7 columns wide, but D:H is only 5 columns. You have confused me now.

[MRichmond]

Sorry if i've confused the issue, the range would then become D:J or F:L, the blocks would always be seven columns wide regardless of the starting column. And it would only be these seven columns that would be being sorted.

[Bob Phillips]

Okay, got that. Question now is, will all worksheets start at D, or could some start at B (ore even C, Z)?

[MRichmond]

The data blocks that need sorting will normally start in either B,C, or D, (on one worksheet it's B, on another it's C and on a third it's D) but on occasion it could be something else but as stated they will always be 7 columns wide

[Bob Phillips]

Here you are



Sub SortData()
Dim StartCell As Range
Dim Lastrow As Long
Dim Grouplast As Long
Dim StartCol As Long
Dim SortCol1 As Long
Dim SortCol2 As Long

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
StartCol = Application.Match("Branch #", .Rows(1), 0)
SortCol1 = Application.Match("% In Branch", .Rows(1), 0)
SortCol2 = Application.Match("In Stock", .Rows(1), 0)

Set StartCell = .Cells(2, StartCol)
Do

Grouplast = StartCell.End(xlDown).Row
StartCell.Resize(Grouplast - StartCell.Row + 1, 7).Sort _
key1:=StartCell.Offset(0, 6), order1:=xlDescending, _
key2:=StartCell.Offset(0, 2), order2:=xlDescending, _
Header:=xlNo
Set StartCell = StartCell.Offset(Grouplast - StartCell.Row + 2)
Loop While StartCell.Row <= Lastrow
End With
End Sub


BTW, will you want to sort multiple sheets in one sweep?

[MRichmond]

XLD you are the master, thanks very much for your efforts.

I am ok sorting each sheet seperately as sometimes there is no sheet 3 and sometimes there are more sheets (but not every sheet in the workbook would need sorting)

Thanks again:beerchug:

[Bob Phillips]

It would be simple to add a sheet loop to cater for all needs.