Auto sort sheet while excluding specific rows

[butlerkd52]

Hi, first time posting! I'm attempting to auto sort sheet 2(col A, then col B) alphabetically that is pre-populated based on criteria selected from sheet 1 while ignoring specific keywords(Select) in sheet 2 and pushing those rows to the bottom of the list. I have been able to sort the data A-Z, but I cannot figure out the exclusion part of it.

What would work best, formula or macro?

Thanks in advance for your help!


The first table represents the raw data while the second table represents what I want the list to be sorted to. Col A sorted A-Z, then Col B sorted A-Z, all while ignoring everything that starts with "Select" and moves them to the bottom of the list.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Header 1[/TD]
[TD]Header 2[/TD]
[/TR]
[TR]
[TD]Vegetable[/TD]
[TD]Carrot[/TD]
[/TR]
[TR]
[TD]Select Fruit or Vegetable[/TD]
[TD]Select Fruit or Vegetable[/TD]
[/TR]
[TR]
[TD]Fruit[/TD]
[TD]Pineapple[/TD]
[/TR]
[TR]
[TD]Fruit[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Vegetable[/TD]
[TD]Onion[/TD]
[/TR]
[TR]
[TD]Select Fruit or Vegetable[/TD]
[TD]Select Fruit or Vegetable[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Header 1[/TD]
[TD]Header 2[/TD]
[/TR]
[TR]
[TD]Fruit[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Fruit[/TD]
[TD]Pineapple[/TD]
[/TR]
[TR]
[TD]Vegetable[/TD]
[TD]Carrot[/TD]
[/TR]
[TR]
[TD]Vegetable[/TD]
[TD]Onion[/TD]
[/TR]
[TR]
[TD]Select Fruit or Vegetable[/TD]
[TD]Select Fruit or Vegetable[/TD]
[/TR]
[TR]
[TD]Select Fruit or Vegetable[/TD]
[TD]Select Fruit or Vegetable[/TD]
[/TR]
</tbody>[/TABLE]

 

[NickRed18]

I feel a macro would be the simplest here.

Move all strings that start with "Select" to the bottom, and then:

Range.Sort()
https://msdn.microsoft.com/VBA/Excel-VBA/articles/range-sort-method-excel

 

[lrobbo314]

*Edit

 

[lrobbo314]

Ok, this one should work. It's not the most elegant solution and I wouldn't recommend it with really large data sets, but it should do it.

Sub cSort(r As Range)
Dim AR()
AR = r.Value
Dim tmp As String
Dim sCnt As Long
For i = 1 To UBound(AR)
    If InStr(AR(i, 1), "Select") > 0 Then
        sCnt = sCnt + 1
    End If
Next i
For i = 1 To UBound(AR)
    For j = i To UBound(AR)
        If InStr(AR(i, 1), "Select") > 0 Then
            tmp = AR(i, 1)
            AR(i, 1) = AR(j, 1)
            AR(j, 1) = tmp
        End If
    Next j
Next i
For i = 1 To UBound(AR) - sCnt
    For j = i To UBound(AR) - sCnt
        If AR(i, 1) > AR(j, 1) Then
            tmp = AR(i, 1)
            AR(i, 1) = AR(j, 1)
            AR(j, 1) = tmp
        End If
    Next j
Next i
r.Value = AR
End Sub
Sub test()
cSort Range("A2:A7")
End Sub

 

[Akuini]

Here's another way:

Sub a1011678a()
Dim ra As Long, rb As Long, rr As Long
rr = Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
Range(Cells(1, "A"), Cells(rr, "B")).Sort key1:=Range("A1"), order1:=xlAscending, key2:=Range("B1"), order2:=xlAscending, _
            Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

ra = Range("A:A").Find("Select*", SearchOrder:=xlByRows, SearchDirection:=xlNext).row
rb = Range("A:A").Find("Select*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row

Range(Cells(ra, "A"), Cells(rb, "B")).Cut Cells(rr + 1, "A")
Range(Cells(rb + 1, "A"), Cells(rr + rb - ra + 1, "B")).Cut Cells(ra, "A")

End Sub

 

[CalcSux78]

Good posts... Here's another way too:

Sub SpecialSort()
Dim rng As Range


Set rng = [A1].CurrentRegion


rng.Replace What:="select", Replacement:="ZZZZZZ", LookAt:=xlPart, _
        SearchOrder:=xlByRows
rng.Sort Key1:=[A1], Order1:=xlAscending, Key2:=[B1], Order2:=xlAscending, Header:=xlYes
rng.Replace What:="ZZZZZZ", Replacement:="Select", LookAt:=xlPart, _
        SearchOrder:=xlByRows
End Sub

 

[butlerkd52]

Thank you for the responses! I'm hoping to be able to carve out some time today to put these to use!

 

[butlerkd52]

Good posts... Here's another way too:

Sub SpecialSort()
Dim rng As Range


Set rng = [A1].CurrentRegion


rng.Replace What:="select", Replacement:="ZZZZZZ", LookAt:=xlPart, _
        SearchOrder:=xlByRows
rng.Sort Key1:=[A1], Order1:=xlAscending, Key2:=[B1], Order2:=xlAscending, Header:=xlYes
rng.Replace What:="ZZZZZZ", Replacement:="Select", LookAt:=xlPart, _
        SearchOrder:=xlByRows
End Sub

Hi CalcSux78, I am now in need of an another header row to my data set and I am unable to get you code to work as expected. I've tried a few variations of changing the rng, Key's, and Header with no luck. How would you sort the same dataset, but with 2 header rows?

Thanks!