Order rows and then hide rows that I don't need

[StanAalbers]

Dear all,

I have a code that is working, but it takes a lot of time with ± 6,000 rows and this is merely the test run...
Question is: is there a more efficient way and when not, can I make the actions invisible so that the user doesn't go into anaphylactic shock while the report is running...

Cells A12 - Q.... contains my table with shipment information. The user can select the following:
C4 = the appropriate center it needs to display (Validation list)
C6 = week nr
c8 = sort by (delivery accuracy, concerns etc.)

This is my code:

Sub sort()
'Hide all the rows
Range("13:65536").EntireRow.Hidden = False
Dim c As Long
    Select Case UCase(Range("c8").Value)
        Case "CLAIMS"
            c = 13
        Case "DELIVERY ON TIME"
            c = 14
        Case "P/U CONCERNS"
            c = 15
        Case Else
            c = 16
    End Select
    If c > 0 Then
    Range("a12:p" & Cells(Rows.Count, "A").End(xlUp).Row).sort Key1:=Cells(2, c), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, Orientation:=xlTopToBottom
    End If
    hide_unwanted
    End Sub
Sub hide_unwanted()
Range("13:65536").EntireRow.Hidden = True
'Hide all the rows that are not part of this center
Dim center As Range
[B]Set center = Range("Q12:Q6000")[/B]
For Each cell In center
   If cell <> "" Then
   If cell.Value = Range("c4") & Range("c6") Then
    cell.EntireRow.Hidden = False
    End If
    End If
    Next cell
End Sub

Additional comment I: can I combine the sorting with a choice between Ascending and Descending? Couldn't get that one to work...
Additional comment II: the bold statement is now static, but it didn't seem to work when I made it flexible as in the "select-code", don't know why.

Thx in advance

 

[Peter_SSs]

Stan

I've had to take a few guesses about just what is in your sheet and what you are trying to do. However, a couple of general comments:

a) It is not a good idea to name a sub (or variable) using one of vba's reserved words (eg Sub sort())

b) Using AutoFilter will be a lot faster than looping through the rows individually.

Give this a try in a copy of your workbook.

First manually unhide all the rows, then try the code below.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> SortAndHide()<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> center <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> center = Range("Q12:Q" & Range("A" & Rows.Count).End(xlUp).Row)<br>    center.AutoFilter Field:=1<br>    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> UCase(Range("c8").Value)<br>        <SPAN style="color:#00007F">Case</SPAN> "CLAIMS"<br>            c = 13<br>        <SPAN style="color:#00007F">Case</SPAN> "DELIVERY ON TIME"<br>            c = 14<br>        <SPAN style="color:#00007F">Case</SPAN> "P/U CONCERNS"<br>            c = 15<br>        Case <SPAN style="color:#00007F">Else</SPAN><br>            c = 16<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>    Range("A12:Q" & Cells(Rows.Count, "A").End(xlUp).Row).sort _<br>        Key1:=Cells(12, c), Order1:=xlAscending, Header:=xlYes, _<br>        OrderCustom:=1, Orientation:=xlTopToBottom<br><br>    center.AutoFilter Field:=1, Criteria1:=Range("C4").Value & Range("C6").Value<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>