question

MichaelBurns-7339 avatar image
0 Votes"
MichaelBurns-7339 asked OssieMac answered

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

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!


office-vba-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

OssieMac avatar image
0 Votes"
OssieMac answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.