We help IT Professionals succeed at work.

Consolidation of repeat commands in Excel macro

313 Views
Last Modified: 2008-01-09
I have an Excel macro that sorts data from various workbooks, compliling it into a single cumulative workbook. The following series of commands (and other similar ones) is repeated throughout the macro:

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
   
It just seems inefficient to see those same lines repeated over an over throughout the macro. Is there a way to assign a "shortcut" to the series and just insert the shortcut instead? If so it would shorten the entire macro by several hundred lines. Thanks.
Comment
Watch Question

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

Author

Commented:
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"

Author

Commented:
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"?

Author

Commented:
Sorry, dirk, you lost me... Can you give me an example of how you would call it?
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Got it, thanks!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.