VBA - Sorting

[kathleen0927]

Hi guys! I am having trouble figuring out how to do the following. Basically, I have a spreadsheet that I need to add controls to so the end user can re-sort the log based on how they want to see the data.

Rows 1:6 are headers
Print Range is A7:AG1525, but range will grow as new rows are inserted

The first sort is by Work Area and criteria is:
1. Filter data in column AE where value = Open or Work Area or Legend. All other rows are to be hidden
2. Resort the print range by Column C (low to high), then a secondary sort by Column E (low to High)
3. Hide column M
4. Unhide Column G

The second (or default view) brings the spreadsheet back to the original view by Vendor with criteria:
1. Filter data in column AE where value = Open or Legend or Vendor. All other rows are to be hiddne
2. Re-sort the Print Range by Column B (Low to High) then a secondary sort by Column E (Low to High)
3. Unhide column M
4. Hide Column G.

Any ideas? Thanks so much!

 

[pbornemeier]

The Print Range can be subset of the data in the worksheet. I wasn not sure that is what you really meant.
This is for the first view

Option Explicit

Sub FirstSort()

    Dim lLastRow As Long
    With ActiveSheet
        
        .AutoFilterMode = False
        lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("A6:AG" & lLastRow).AutoFilter Field:=31, Criteria1:=Array( _
            "Legend", "Open", "Work Area"), Operator:=xlFilterValues
    
        .AutoFilter.Sort.SortFields.Clear
        .AutoFilter.Sort.SortFields.Add Key:=Range _
            ("C7:C" & lLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        .AutoFilter.Sort.SortFields.Add Key:=Range _
            ("E7:E" & lLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With .AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        .Columns("M:M").EntireColumn.Hidden = True
        .Columns("G:G").EntireColumn.Hidden = False
        
    End With
        
End Sub

 

[kathleen0927]

oh wow...thank you so much! I will try this and let you know how it goes

 

[kathleen0927]

Thanks. This is excellent! I was able to use this code to create the second filter back to the original sort. But I have 1 problem/ question: for the second filter, I need to have all the rows, including those I filtered to hide, back in the original numerical sort order (By column B then E) then I need to filter to show only rows that have Legend, Open or Vendor in column 31 but I can’t seem to do that. Should I take the auto filter off, run the re-sort code then filter to show only the ones that are Legend, Open or Vendor?

 

[shg]

How about ...

Sub ChangeView()
  Static iView      As Long
  Dim wks           As Worksheet
  Dim r             As Range

  iView = IIf(iView = 1, 2, 1)
  Set wks = ActiveSheet

  With wks
    If .AutoFilterMode Then .AutoFilterMode = False
    Set r = .UsedRange.Offset(5)

    r.Sort Key1:=IIf(iView = 1, r.Range("C1"), r.Range("B1")), _
           Key2:=.Range("E1"), _
           Header:=xlYes

    r.AutoFilter Field:=31, _
                 Criteria1:=Array("Legend", "Open", "Work Area"), _
                 Operator:=xlFilterValues
    .Columns("M").Hidden = iView = 1
    .Columns("G").Hidden = iView <> 1
  End With

  MsgBox IIf(iView = 1, "Work area", "Vendor")
End Sub

 

[kathleen0927]

Oooohhh...I figured it out but I think your code maybe more streamline than how I did it. Thank you so much for your help. Enjoy your weekend!