Consolidation of repeat commands in Excel macro

If you make a subroutine like:

sub sortCall()

Range("J1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=10, Criteria1:="G"
    Selection.AutoFilter Field:=21, Criteria1:="=D", Operator:=xlOr, _
        Criteria2:="=S" 'dismissed or suspended
    Set UpperLeftCorner = Sheets("schonfeld_012507").Range("A1")
         RowCount = -1
        For Each area In _
           UpperLeftCorner.CurrentRegion.SpecialCells(xlVisible).Areas
           RowCount = RowCount + area.Rows.Count
end sub

then you can call sortCall in place of the above code.

However, if the lines are not exactly the same (ie. different paramaters to the sort, etc) you will require arguments.

Ie.

Sub sortCall(fld1 as integer, crit1 as string)

Range("J1").Select
    Selection.AutoFilter

    Selection.AutoFilter Field:=fld1, Criteria1:=crit1

    Selection.AutoFilter Field:=21, Criteria1:="=D", Operator:=xlOr, _
        Criteria2:="=S" 'dismissed or suspended
    Set UpperLeftCorner = Sheets("schonfeld_012507").Range("A1")
         RowCount = -1
        For Each area In _
           UpperLeftCorner.CurrentRegion.SpecialCells(xlVisible).Areas
           RowCount = RowCount + area.Rows.Count
end sub

Okay - I think I have the premise but I'm not sure how to put it into practice. You did - thank you! - bring up a good point. Looking at the macro the first instance of the series is:

Range("J1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=10, Criteria1:="G"
    Selection.AutoFilter Field:=21, Criteria1:="=D", Operator:=xlOr, _
        Criteria2:="=S" 'dismissed or suspended
    Set UpperLeftCorner = Sheets("schonfeld_012507").Range("A1")
         RowCount = -1
        For Each area In _
           UpperLeftCorner.CurrentRegion.SpecialCells(xlVisible).Areas
           RowCount = RowCount + area.Rows.Count
       Next
     


But the next instance is:

   Range("J1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=10, Criteria1:="U"
    Selection.AutoFilter Field:=21, Criteria1:="=D", Operator:=xlOr, _
        Criteria2:="=S" 'dismissed or suspended
    Set UpperLeftCorner = Sheets("schonfeld_012507").Range("A1")
         RowCount = -1
        For Each area In _
           UpperLeftCorner.CurrentRegion.SpecialCells(xlVisible).Areas
           RowCount = RowCount + area.Rows.Count
       Next
 

So everything has changed except in the second instance I am looking for "U" instead of "G" in field 10. Where I lost you is trying to figure out where in the process I should substitute the U for the G. Thanks again.,

ok. basically you will need the sub to look like this:

sub sortCall(crit as string)

 Range("J1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=10, Criteria1:=crit
    Selection.AutoFilter Field:=21, Criteria1:="=D", Operator:=xlOr, _
        Criteria2:="=S" 'dismissed or suspended
    Set UpperLeftCorner = Sheets("schonfeld_012507").Range("A1")
         RowCount = -1
        For Each area In _
           UpperLeftCorner.CurrentRegion.SpecialCells(xlVisible).Areas
           RowCount = RowCount + area.Rows.Count
       Next
end sub

basically, I ask for crit as a paramater to the new sub, and I replace "U" with crit in the criteria="U" statement.

so now, when you call sortCall, you will use

sortCall "U"

or

sortCall "G"

I've set the sub up as follows:

Sub sortCall(crit1 As String)

 Range("J1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=10, Criteria1:=crit1
    Selection.AutoFilter Field:=21, Criteria1:="=G", Operator:=xlOr, _
        Criteria2:="=S" 'dismissed or suspended
    Set UpperLeftCorner = Sheets("schonfeld_012507").Range("A1")
         RowCount = -1
        For Each area In _
           UpperLeftCorner.CurrentRegion.SpecialCells(xlVisible).Areas
           RowCount = RowCount + area.Rows.Count
       Next
End Sub

and I'm calling it by:

 
    Application.Run "'Non-Reg.xls' !sortCall"
    crit1 = "g"
     

and I am getting an error message "Macro "'Non-Reg.xls' !sortCall" cannot be found. Non-Reg.xls is the name of the workbook with the macro. I'm sure its me, any ideas?

I've never called a macro that way. Why can't you just call sortCall "g"?

Sorry, dirk, you lost me... Can you give me an example of how you would call it?

Got it, thanks!