Auto Filtering with Auto sort on opening

[Mike Daniels]

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

 

[Peter_SSs]

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]

Peter

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

Sub Autpen()

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]

Peter

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

Sub Autpen()

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]

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]

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> Autpen()
    <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]

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]

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