Auto Filtering with Auto sort on opening

Mike Daniels

Board Regular
Joined
Feb 28, 2006
Messages
73
Worksheet Sort takes its data from Worksheet Roster and does a very nice alphabetical sort on column C when it opens. If it is not fully populated (300 rows), the lower rows are blank in column C. Would like to eliminate all rows without data in column C.

Auto filter does it well, but it needs to be manually launched after the data is copied over and sorted. Is there a way that this filter can be initiated after the copy and sort macros do their thing?

Mike
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Worksheet Sort takes its data from Worksheet Roster and does a very nice alphabetical sort on column C when it opens. If it is not fully populated (300 rows), the lower rows are blank in column C. Would like to eliminate all rows without data in column C.

Auto filter does it well, but it needs to be manually launched after the data is copied over and sorted. Is there a way that this filter can be initiated after the copy and sort macros do their thing?

Mike
Mike

Something along these lines might help you:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Test()

    <SPAN style="color:#007F00">' Copy and sort code goes here</SPAN>
    
    <SPAN style="color:#00007F">With</SPAN> Worksheets("Sort").Range("C1:C300")
        .AutoFilter Field:=1, Criteria1:="<>"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

Mike Daniels

Board Regular
Joined
Feb 28, 2006
Messages
73
Peter

Didn't work. Here is the code as I think you said it should be entered.

Sub Auto_Open()

Rows("2:296").Select
Selection.Sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

With Worksheets("Sort").Range("C1:C296")
.AutoFilter Field:=1, Criteria1:="<>"
End With

End Sub

This is the message received.

Run-time error ‘9’:
Subscript out of range

Mike
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Peter

Didn't work. Here is the code as I think you said it should be entered.

Sub Auto_Open()

Rows("2:296").Select
Selection.Sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

With Worksheets("Sort").Range("C1:C296")
.AutoFilter Field:=1, Criteria1:="<>"
End With

End Sub

This is the message received.

Run-time error ‘9’:
Subscript out of range

Mike
Mike, a few questions:

1. What is the name of the worksheet that the sorting of rows 2:296 applies to? Is it "Sort"? What other sheets are in this workbook?

2. What line is highlighted when the Run-time error occurs and you choose "Debug"?

3. Can you show us or tell us a bit about your data? Is there nothing in row1 or headings? What is in row 2? What is in column C and why is C4 the sort key? What is in row 3? All questions just to try to get to understand what you are faced with.
 

Mike Daniels

Board Regular
Joined
Feb 28, 2006
Messages
73

ADVERTISEMENT

Peter
(Excel 2000)
1. Worksheet is named “285-Alpha Sort”. Its data is copied directly from 285-ROSTER tab (ROSTER BY TEE) rows 4 thru 271 to 285-Alpha Sort rows 2 thru 269, and from 285-ROSTER tab (ROSTER – SINGLES) from rows 4 thru 30 (which are copied to rows 270 thru 296 of 285-Alpha Sort).
2. With Worksheets("Sort").Range("C1:C296")
3. Peter, I think since I have tried to resolve this issue, It looks like I have made some changes that maybe aren’t reflected in the Macro code. Sorry, I have tried so many things I appear to be getting lost. But these are true:

Column A of all sheets is Blank

Row 1of 285-Alpha Sort is Headings
Row 2B:2I is Data
Row 2 Column C is Last Name
Rows 3 thru 296 are data

Row 1 of 285-ROSTER (ROSTER BY TEE) is blank
Row 2 is header data
Row 3 is header (same as Alpha Sort)
Row 4B:4I is data (same as Row 2 of Alpha Sort)
Rows 5 thru 271 are data

Row 1 of 285-ROSTER (ROSTER - SINGLES) is blank
Row 2 is header data
Row 3 is header (same as Alpha Sort)
Row 4B:4I is data (same as Row 2 of Alpha Sort)
Rows 5 thru 30 are data

Column E of all is hidden and has the code) =IF('[285-ROSTER.xls]ROSTER BY TEE'!E4>" ",'[285-ROSTER.xls]ROSTER BY TEE'!E4,NA()) from row 2 thru 269 and =IF('[285-ROSTER.xls]ROSTER - SINGLES'!E4>" ",'[285-ROSTER.xls]ROSTER - SINGLES'!E4,NA())

I’m still a rookie, how do I display the worksheet(s) for you to view and manipulate in this forum? I truly thank you for your time and consideration.

Mike
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
1. Worksheet is named “285-Alpha Sort”.
Well, one thing that would definitely need to change in my suggested code is the name of the Worksheet (from "Sort" to "285-Alpha Sort").

However, ther may be some more simplification. See if this works:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Auto_Open()
    <SPAN style="color:#00007F">With</SPAN> Worksheets("285-Alpha Sort")
        .Rows("2:296").Sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        .Range("C1:C300").AutoFilter Field:=1, Criteria1:="<>"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

Mike Daniels

Board Regular
Joined
Feb 28, 2006
Messages
73
Peter,

Same message Runtime Error '9'
sub script out of range
With Worksheets "285-Alpha Sort" (a flashing bar cursor at the front)

How can I send a sample of each worksheet for you to see?

Mike

I am obviiously a rank rookie with VB (have only previously used recorded ones), and I appreciate your patience.
 

Mike Daniels

Board Regular
Joined
Feb 28, 2006
Messages
73
Peter,

I can't thank you enough. It works perfectly; exactly as I envisioned. Again my apologies for some misinformation early, I learned a great deal from this post. Thanks for your efforts ...

Best regards,

Mike
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,329
Members
414,445
Latest member
walramgo02

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