Trying to understand a macro that was recorded

[RSW]

I have a large Excel spreadsheet that I've been asked to expand from 25 to 100 sections. The spreadsheet has a complicated macro that was recorded. Rather than attempt to record a whole new one, I'm trying to understand the VBA so that I can modify the existing macro.

Here is the first part of the macro (this kind of thing repeats over and over)

Cells.Select
    Selection.AutoFilter
    ActiveWindow.SmallScroll ToRight:=22
    Selection.AutoFilter Field:=33, Criteria1:="0"
    Rows("26:561").Select
    Range("W54").Activate
    ActiveWindow.SmallScroll Down:=9
    Selection.Delete Shift:=xlUp
    ActiveWindow.LargeScroll ToRight:=-2
    Selection.AutoFilter Field:=1
    Cells.Select
    Selection.AutoFilter
    Range("A1").Select
    Sheets("Material Cost Estimates").Select
    Cells.Select
    Selection.AutoFilter
    ActiveWindow.SmallScroll ToRight:=2
    ActiveWindow.LargeScroll ToRight:=-2
    ActiveWindow.SmallScroll ToRight:=20

I have looked around all over the Internet but cannot figure out:

- In (for example) "Selection.AutoFilter Field:=33", what exactly does the number 33 signify? I can't tell...not even from looking at the spreadsheet and knowing what the overall macro is supposed to do!

- I looked up the difference between SmallScroll and LargeScroll...but how in the world is LargeScroll reliable in a macro? It scrolls number of "pages"...but doesn't the number of pages depend on the user's resolution, Excel settings, window size, etc?

- What is the difference between Select and Activate here?

- What do the Selection.Autofilter's that have no criteria accomplish? Anything?

- This macro DOES work for the current version of the spreadsheet, but does all of this recorded code even make SENSE?

Thanks in advance for any help anyone can provide!!

 

[Bilbo_Baggins_Esq]

In general, recorded macros are seldom applicable to other workbooks.
Excel records litteral references and actions.

They can be useful as a basis from which variables can be defined and used to replace many of the litteral refereneces in recorded macro.

For example, you are correct about LargeScroll being dependent on the screen resolution.

I would suggest that you try to step through the code using F8 and watch what each step does.
Make notes if you need to.
I would think that if properly modified, the scrolls can be eliminated. They were only recorded as the user was scrolling to find the desired filter to apply with mouse clicks.
Filters can be applied even if the desired filter is not visible to the user.

The field numbers refer to the column within the autofilter range.
You'll see what each step does if you step through the code line by line.

 

[smiler44]

At a guess the recorded macro did what it was asked to do and that was to record the actions. I think some of the actions are not required.
ActiveWindow.SmallScroll ToRight:=22 this seems to move the 22nd column from column A to the very left of the screen, the 22nd column to the left on column A is column w

Rows("26:561").Select rows 26 to 561 are selected but the macro then selects Range("W54").Activate . I would say Rows("26:561").Select is not required
ActiveWindow.SmallScroll Down:=9 looks to scroll down 9 rows from w54
cell 54 is deleted and the cells below move up.

hope this is of some help.

copy and paste your code above and stick it to a command button on perhaps a new spread sheet and run the code. after each line of code put in msgbox("hi") this will stop the code running and allow you to see what has heppened.
smiler44

 

[noboffinme]

Hi Can you attach the file that contains this Macro?

I haven't been able to exactly replicate the Macro in the recorder but the code (apart from scrolls) appears to apply the Autofilter, select Rows 26-561 & then open the worksheet "Material Cost Estimates" & selects Autofilter there & then some other scrolling....

Generally if I have time, I remove any 'scrolling code' to speed up the Macro & this has no effect on the code.

If you attach the file, I can run the code & it would help if you include your expectation (result) of the Macro.

 

[RSW]

Thanks all,

I am trying to work my way through this. Does anyone know what the "Field:=33" et al means? That seems like it should be easy to answer, and yet I can't find a thing about it nor figure it out based on the spreadsheet.

noboffinme: I wish I could attach the file, but the spreadsheet is proprietary.

 

[noboffinme]

OK, I'd then check the code line by line as asuggested to see exactly what it does.

 

[chergh]

From the helpfile:

AutoFilter Method


Filters a list using the AutoFilter.

Note Apply the AutoFilter property to a Worksheet object to return an AutoFilter object.

Syntax

expression.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)

expression Required. An expression that returns a Range object.

Field Optional Variant. The integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field one).

Criteria1 Optional Variant. The criteria (a string; for example, "101"). Use "=" to find blank fields, or use "<>" to find nonblank fields. If this argument is omitted, the criteria is All. If Operator is xlTop10Items, Criteria1 specifies the number of items (for example, "10").

Operator Optional Variant. Can be one of the following XlAutoFilterOperator constants: xlAnd, xlBottom10Items, xlBottom10Percent, xlOr, xlTop10Items, or xlTop10Percent. Use xlAnd and xlOr with Criteria1 and Criteria2 to construct compound criteria.

Criteria2 Optional Variant. The second criteria (a string). Used with Criteria1 and Operator to construct compound criteria.

VisibleDropDown Optional Variant. True to display the AutoFilter drop-down arrow for the filtered field. False to hide the AutoFilter drop-down arrow for the filtered field. True by default.

 

[RSW]

I want to thank you all for your assistance. Stepping through the code was a great help, and as several of you guessed, some of the code was totally unnecessary. It turns out that all this macro really does is delete a bunch of information from a bunch of different worksheets, and then make a Summary worksheet visible! (I had thought the macro was actually doing the Summary calculations and creating the page...but it turns out that worksheet was there the whole time doing its thing, just hidden.)