Data - Group - Hide Details

[Dan]

Trying to include the "Hide details" command in a macro but with variables

Dim Ctr1 As Variant
Dim Ctr2 As Variant

Ctr1 = 8
Ctr2 = 23

Rows(Ctr1 & ":" & Ctr2).Select
worksSelection.Rows.Group
ExecuteExcel4Macro "SHOW.DETAIL(1,23,FALSE,,7)" - works

ExecuteExcel4Macro "SHOW.DETAIL(1,Ctr2,FALSE,,Ctr1 - 1)" - does not work
Get a Run-time error '1004' -You've entered too many arguments for this
equation.

Any ideas?

 

[Dave Peterson]

How about:

ExecuteExcel4Macro "SHOW.DETAIL(1," & ctr2 & ",FALSE,," & ctr1-1 & ")"

Untested!

And why not declare them as longs?

 

[Dan]

Now I have more issues. I generated the macro on Excel 2002 (by using the
record command) and replaced the variables myself. As for the Variant vs Long
- no reason just wasn't paying that much attention when I created the
variables to test.

Now running the macro in 2003 generates the same error for both
"ExecuteExcel4Macro" commands (even the one generated by the record command).
Both still do not "hide" the details.

 

[Dan]

Found a potential solution around the "ExecuteExcel4Macro" error. In place of
hiding the details for each set of data you can use the
"ActiveSheet.Outline.ShowLevels RowLevels:=1" command to hide all details for
all groups.

Still not sure how to hide for specific groups.

Help states:
Remarks
The Microsoft Excel 4.0 macro isn't evaluated in the context of the current
workbook or sheet. This means that any references should be external and
should specify an explicit workbook name. For example, to run the Microsoft
Excel 4.0 macro "My_Macro" in Book1 you must use "Book1!My_Macro()". If you
don't specify the workbook name, this method fails

Not sure on how this applies.

 

[Dave Peterson]

It doesn't look like your show.detail line is using the correct syntax.

I opened up the old XL4 help file and found this:

SHOW.DETAIL(rowcol, rowcol_num, expand, show_field)
Rowcol is a number that specifies whether to operate on rows or columns of
data.

Rowcol Operates on

1 Rows
2 Columns
3 The current cell's row or column. The second argument, rowcol_num, is then
ignored.

Rowcol_num is a number that specifies the row or column to expand or
collapse. If you are in A1 mode, you must still give the column as a number. If
rowcol_num is not a summary row or column, SHOW.DETAIL returns the #VALUE! error
value and interrupts the macro.
Expand is a logical value that specifies whether to expand or collapse the
detail under the row or column. If expand is TRUE, Microsoft Excel expands the
detail under the row or column; if FALSE, it collapses the detail under the row
or column. If expand is omitted, the detail is expanded if it is currently
collapsed and collapsed if it is currently expanded.
Show_Field is a string specifying the name of the field to add to a
PivotTable, if the selection is inside a PivotTable. The new field is added as
the new innermost field. Available for only innermost row or column fields.

You may want to google for show.detail to see some examples.

 

[Dan]

Dave,

Thanks for the info. I will look around fo some examples.
Not sure why the syntax would be the problem since it was obtained from the
Record macro function to see how Excel defined it. I just re-ran it and it
failed.

Thanks again for all the help.