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

 

[jolivanes]

I am sorry. Your explanation does not make sense to me but saying that, there might be other things why you want it that way.
To me it does not make sense why you would for instance sort every 2nd column of +/- 115 columns and then come back and do the ones you did not do in the first go around.
However, I'll have a look at it.
Like hiker95 mentions in his post, it is so much easier to have a before and after workbook to work with but that has to come from you.

 

[Eric W]

Maybe:

Sub SortCols()
Dim FirstCol As Long, LastCol As Long, c As Long, lr As Long, TopRow As Long

    FirstCol = 7
    LastCol = 122
    TopRow = 4
    
    lr = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
    
    With ActiveSheet.Sort
        .SortFields.Clear
        For c = FirstCol To LastCol
            If Cells(TopRow, c).Value Like "*hello*" Then .SortFields.Add Key:=Cells(TopRow, c)
        Next c
        For c = FirstCol To LastCol
            If Not Cells(TopRow, c).Value Like "*hello*" Then .SortFields.Add Key:=Cells(TopRow, c)
        Next c
        .SetRange Range(Cells(TopRow, FirstCol), Cells(lr, LastCol))
        .Header = xlYes
        .Apply
    End With
End Sub

although without seeing your data, I can't quite imagine what this would do for you.

 

[Eisasuarez]

Sorry - as soon as i get access to the workbook il post a copy of data

I can sort all columns in order in 1 go like the code you have provided (thank you) and then the columns that have the word hello - order them
1st (to the top)

 

[Eisasuarez]

Maybe:

Sub SortCols()
Dim FirstCol As Long, LastCol As Long, c As Long, lr As Long, TopRow As Long

    FirstCol = 7
    LastCol = 122
    TopRow = 4
    
    lr = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
    
    With ActiveSheet.Sort
        .SortFields.Clear
        For c = FirstCol To LastCol
            If Cells(TopRow, c).Value Like "*hello*" Then .SortFields.Add Key:=Cells(TopRow, c)
        Next c
        For c = FirstCol To LastCol
            If Not Cells(TopRow, c).Value Like "*hello*" Then .SortFields.Add Key:=Cells(TopRow, c)
        Next c
        .SetRange Range(Cells(TopRow, FirstCol), Cells(lr, LastCol))
        .Header = xlYes
        .Apply
    End With
End Sub

although without seeing your data, I can't quite imagine what this would do for you.

Again thank you for all your help and i will try and post the example as soon as i get access to the workbook

with your example Eric - it looks like it only looks in column number Row 4 at all times to see if the word hello appears

I need to look in that column to see if the word hello appears - eg

if hello is found in range(cells(4,c),cells(lastrow,c) then sort that column or sort everycolumn A-Z and then order the columns that have the world hello to the top

again appreciate your help and apologies as ive not got the workbook in hand and making it difficult to explain - sorry

 

[Eric W]

Sub SortCols()
Dim FirstCol As Long, LastCol As Long, c As Long, lr As Long, TopRow As Long

    FirstCol = 1
    LastCol = 8
    TopRow = 4
    
    lr = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
    
    With ActiveSheet.Sort
        .SortFields.Clear
        For c = FirstCol To LastCol
            If WorksheetFunction.CountIf(Range(Cells(TopRow, c), Cells(lr, c)), "*hello*") > 0 Then .SortFields.Add Key:=Cells(TopRow, c)
        Next c
        For c = FirstCol To LastCol
            If WorksheetFunction.CountIf(Range(Cells(TopRow, c), Cells(lr, c)), "*hello*") = 0 Then .SortFields.Add Key:=Cells(TopRow, c)
        Next c
        .SetRange Range(Cells(TopRow, FirstCol), Cells(lr, LastCol))
        .Header = xlYes
        .Apply
    End With
End Sub

 

[jolivanes]

Re: and then the columns that have the word hello - order them 1st (to the top)
What does that mean? Do the Columns need to be moved? You can't move a column to the top (which signifies up), just left or right.

Sub AAAAA()
Dim i As Long, ii As Long
Application.ScreenUpdating = False

    For i = 7 To 18
        If WorksheetFunction.CountIf(Columns(i), "Hello") <> 0 Then
            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
        End If
    Next i

    For ii = 7 To 18
        If WorksheetFunction.CountIf(Columns(ii), "Hello") = 0 Then
            Range(Cells(4, ii), Cells(Cells(Rows.Count, ii).End(xlUp).Row, ii)).Sort Key1:=Cells(4, ii), Order1:=xlAscending, _
        Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        End If
    Next ii
    
Application.ScreenUpdating = True
End Sub

 

[Eisasuarez]

Sorry my explanation is bad - what i meant is that when you click on sort manually- you can order the sort to which column takes preference - so i mean sort order

the reason why i said that was so i could potentially avoid using 2 loops as in the example code provided by Eric

if i could sort each column smallest to largest with possibly the code provided by you and keep a track of the columns that have the word hello, i was thinking of sorting these columns sort order to the top to avoid 2 loops

1) Sort Smallest to Largest in 2 go for all columns
2) Whilst sorting and looping through - store column numbers that have words Hello
3) Move the sort order of these columns to the top to avoid 2 loops

That was my thinking

 

[jolivanes]

I missed Post #6 .
If you're PM'ing people I better stay out of it.
Good luck.

 

[Eisasuarez]

Hi - i PMed before your response

Any help from anyone is appreciated

Thank you