xltrader100
05-07-2013, 08:00 AM
In Excel 11, I have a single column containing many groups of cells (grouped by Row, using Data/Group and Outline/Group...). There are no overlapping groups. I'm using Application.GoTo to scroll to a particular cell in this column, which may be part of group, and if it is then its group may be expanded or collapsed.
If the group is expanded then there's no problem, but if the group is collapsed then GoTo won't expand it, and the GoTo cell is selected but stays hidden.
If my selected cell is in Row-160, and if the group it belongs to extends from Row-152 to Row-183, then if I expand the group manually with the Recorder (using Data/Group and Outline/Show Group), it gives:
ExecuteExcel4Macro "SHOW.DETAIL(1,183,TRUE,,152)"
which works fine, except that in general I won't know what group my GoTo cell belongs to, or where that group starts and stops, or whether that group is currently expanded or collapsed.
If I try to expand all groups, I run into the same problem, and the recorder gives:
Cells.Select
ExecuteExcel4Macro "SHOW.DETAIL(1,1464,TRUE,,1086)"
ExecuteExcel4Macro "SHOW.DETAIL(1,1085,TRUE,,1080)"
ExecuteExcel4Macro "SHOW.DETAIL(1,1079,TRUE,,740)"
ExecuteExcel4Macro "SHOW.DETAIL(1,739,TRUE,,647)"
- etc for 20 more groups.
So how can I expand a group (or all groups) in code to make the GoTo cell visible?
If the group is expanded then there's no problem, but if the group is collapsed then GoTo won't expand it, and the GoTo cell is selected but stays hidden.
If my selected cell is in Row-160, and if the group it belongs to extends from Row-152 to Row-183, then if I expand the group manually with the Recorder (using Data/Group and Outline/Show Group), it gives:
ExecuteExcel4Macro "SHOW.DETAIL(1,183,TRUE,,152)"
which works fine, except that in general I won't know what group my GoTo cell belongs to, or where that group starts and stops, or whether that group is currently expanded or collapsed.
If I try to expand all groups, I run into the same problem, and the recorder gives:
Cells.Select
ExecuteExcel4Macro "SHOW.DETAIL(1,1464,TRUE,,1086)"
ExecuteExcel4Macro "SHOW.DETAIL(1,1085,TRUE,,1080)"
ExecuteExcel4Macro "SHOW.DETAIL(1,1079,TRUE,,740)"
ExecuteExcel4Macro "SHOW.DETAIL(1,739,TRUE,,647)"
- etc for 20 more groups.
So how can I expand a group (or all groups) in code to make the GoTo cell visible?