VBA - quickest way for multiple sort depending on criteria

[Eisasuarez]

Hi All

I have data in columns A4:DR & LastRow (4 being the header row)

I want to be able to look at each column from G onwards and see if the word "Hello" appears in there - if it does, sort smallest to largest based on that column.
Continue to look at every column and if it appears again then also sort that column smallest to largest (A to Z) etc following that pattern all the way through so there should be multiple sorts

If its easy enough to do, I would like the rest of the columns that dont have that word to be sorted smallest to largest also but after the columns being sorted first that has the word hello

I hope this makes sense

Whats the best way to do this

Thanks

 

[Eisasuarez]

This is the thing i was after but cant get it to work - The last bit where i need to sort rest of the columns may not be needed but i would like to have the option there to sort them also - if its not needed i can comment it out

Hoping someone can help me

Thank You

Sub MultiSortFlex()


    Dim ws As Worksheet
    Dim c As Long
    Dim lRow As Long
    
    Set ws = Worksheets("View")
    
    lRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
     
     
    With ws.Range("A4:DR" & lRow)
            For c = 7 To 122
                 If IsNumber(WorksheetFunction.Match("Hello", ws.Cells(lRow, c), 0)) Then
                    'Sort column A to Z
                        .SortFields.Clear
                        .SortFields.Add Key:=ws.Cells(lRow, c), SortOn:=xlSortOnValues, Order:=xlAscending
                        .Header = xlYes
                        .Apply
                    'go to Next Column to find word Hello and sort
                 End If
            Next c
                    'Now sort rest of the columns that didnt have the word hello A To Z
    End With
        


End Sub

 

[jolivanes]

Enlighten us. What is the secret of doing the columns with "Hello" first and the remaining Columns right after. Why not all at once?

 

[Eisasuarez]

Hi - its 1 of the criterias required to sort all columns that have the word hello in it first

If i can sort all at once following this structure that would be awesome

 

[Eisasuarez]

Hi Again,

Ive tried to do it all at once but cant seem to figure it out - hoping someone can help me please

thank you

 

[hiker95]

Eisasuarez,

Thanks for the Private Message.

It would help if we could see your actual raw data workbook/worksheets, and, what the results (manually formatted by you) should look like.

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[jolivanes]

Would this not do it?
If you think it is, make sure to first try it on a copy of your original. There is no "Undo" for code.
All you can do if it is not what you want is close the workbook without saving.

Sub AAAAA()
Dim i As Long
Application.ScreenUpdating = False
    For i = 7 To 122
        Range(Cells(4, i), Cells(Cells(Rows.Count, i).End(xlUp).Row, i)).Sort Key1:=Cells(4, i), Order1:=xlAscending, _
        Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Next i
Application.ScreenUpdating = True
End Sub

 

[Eisasuarez]

Hi - thanks for your response

i can give that a go when i get access to the workbook but having a quick glance at your code reads that it will sort every column in smallest to largest which is fine but i do want the columns that have the hello in there to take preference - there will be columns that dont have that word but where and if does - then that column should be ordered first if that makes sense

so all columns that has hello gets preferenced first and the other columns pushed down the order

so your code will work fine but the order will need to be changed for the columns hello coming 1st

hope that makes sense

 

[Eisasuarez]

Hi Hiker - i dont have access to my workbook at the moment- please see my latest post and previous post by jolivanes - hopefully that might give you sn understanding of what im wanting

Thank you

 

[hiker95]

Hi Hiker - i dont have access to my workbook at the moment- please see my latest post and previous post by jolivanes - hopefully that might give you sn understanding of what im wanting

Thank you

Eisasuarez,

Per my reply #6:

Without seeing your actual raw data, and, what the results should look like, then, I can not help you.