kathleen0927

New Member
Joined
Feb 7, 2018
Messages
32
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!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,889
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


Code:
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

New Member
Joined
Feb 7, 2018
Messages
32
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,784
Office Version
  1. 2010
Platform
  1. Windows
How about ...

Code:
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

New Member
Joined
Feb 7, 2018
Messages
32
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,316
Messages
5,635,521
Members
416,862
Latest member
MGDlite

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top