How to sort data on multiple sheets but only when present?

Question

Hello all,
I am very new to excel and am just getting into the world of VBA but find myself very lost. I am trying to find code that can sort data across multiple sheets, but only when there is data present.

On the first sheet I am trying to sort everything in column D from highest to lowest
On the sheet 2-6 I am trying to sort everything in column B from highest to lowest
The kicker is that every week I will need to delete data from each sheet and paste new data in its place. I was able to find some code on how to sort but when I tried to clear the data out from one sheet everything crashed
I also plan on making a button that can clear out all the data from each sheet, to make it easier to paste in the new data each week
Any info is helpful, thanks!

Answer

I am assuming the following.
First row in all sheets is column headers
You want to sort so that all data on each row remains with the row and that column D or Column B is the key (or column) for sorting.

The code contains a UDF (User Defined Function) for finding the last used row and last used column on the worksheet. This is a reliable method where not all cells are populated to bottom of data range.

I need to confirm what you mean by "plan on making a button that can clear out all the data from each sheet" Do you mean all data under the column headers or just all data in column D for sheet 1 and column B for sheets 2 to 6.

Ensure you have a backup of your workbook in case I have misunderstood the question and it destroys your data.

Sub SortMultipleColumns()
    Dim ws As Worksheet
    Dim lngLastRow As Long
    Dim lngLastCol As Long

    'Following is a simple sort without all of the code when Sort is Recorded.
    'Advantage is that headers can be used when setting Keys.

    For Each ws In ThisWorkbook.Worksheets
        lngLastRow = LastRowOrCol(True, ws.Cells)   'True argument finds last used row on worksheet
        lngLastCol = LastRowOrCol(False, ws.Cells)  'False argument finds last used row on worksheet

        Select Case ws.Index    'Identify the sheet index number

            Case 1      'Test if sheet index is 1
                If WorksheetFunction.CountA(ws.Columns("D:D")) > 1 Then   'Test if more data than just column Header
                    With ws.Sort
                        .SortFields.Clear   'Must Clear Existing Sort or retains previous Sort Parameters
                        .SortFields.Add Key:=ws.Range("D1"), Order:=xlDescending   'Sort Descending on column D
                        .SetRange ws.Range(ws.Cells(1, 1), ws.Cells(lngLastRow, lngLastCol))
                        .Header = xlYes
                        .Apply
                    End With
                End If

            Case 2 To 6     'Test if sheet index is 2 to 6
                If WorksheetFunction.CountA(ws.Columns("B:B")) > 1 Then   'Test if more data than just column Header
                    With ws.Sort
                        .SortFields.Clear   'Must Clear Existing Sort or retains previous Sort Parameters
                        .SortFields.Add Key:=ws.Range("B1"), Order:=xlDescending   'Sort Descending on column B
                        'Following line sets the range to sort from cell(1,1) which is cell A1 to the last used cell on worksheet
                        .SetRange ws.Range(ws.Cells(1, 1), ws.Cells(lngLastRow, lngLastCol))
                        .Header = xlYes
                        .Apply
                    End With
                End If
        End Select
    Next ws

End Sub



Function LastRowOrCol(bolRowOrCol As Boolean, Optional rng As Range) As Long
    'Finds the last used row or column in a worksheet
    'First parameter is True for Last Row or False for last Column
    'Third parameter is optional
    'Must be specified if not ActiveSheet

    Dim lngRowCol As Long
    Dim rngToFind As Range

    If rng Is Nothing Then
        Set rng = ActiveSheet.Cells
    End If

    If bolRowOrCol Then
        lngRowCol = xlByRows
    Else
        lngRowCol = xlByColumns
    End If

    With rng
        Set rngToFind = rng.Find(What:="*", _
                LookIn:=xlFormulas, _
                LookAt:=xlPart, _
                SearchOrder:=lngRowCol, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False)
    End With

    If Not rngToFind Is Nothing Then
        If bolRowOrCol Then
            LastRowOrCol = rngToFind.Row
        Else
            LastRowOrCol = rngToFind.Column
        End If
    End If

End Function

Share via

How to sort data on multiple sheets but only when present?

1 answer