Hi,
I am using code as follows:
Cells.Columns.Ungroup
This works ok, but prior to ungrouping, I need to make sure that the columns are visible first (clicking the + sign).
The macro recorder does not capture the pressing of the + to show all columns. How can I do this in code?
Cells.Columns.Ungroup
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Cells.Columns.Ungroup
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Cells.Columns.Ungroup
Thanks Hans, that works. I tried many variations without sussess:
cells.show
cells.columns.show
.....
May I ask also, where I am going wrong here?
When the sub completes, and the screen updates, for a split second the page view > "ActiveWindow.View = xlNormalView" is correct, but then adjusts to "Page Break Preview" for some unknown reason?
The original document is set as page break preview, which is why I change it to normal at the end of the code.
Even stranger, is that if I step through the code, this does not happen. Any idea's as to the cause?
cells.show
cells.columns.show
.....
May I ask also, where I am going wrong here?
Code: Select all
Set wb0 = Workbooks.Open(Filename:=strPath1 & "Report.xls")
Sheets("Report").Select
Cells.Copy
Cells.PasteSpecial Paste:=xlPasteValues
Sheets("Report").Copy 'Moves to new book
wb0.Close SaveChanges:=False
Cells.Columns.Hidden = False
Cells.Columns.Ungroup
Columns("M:IV").Delete Shift:=xlToLeft
Columns("A:B").Delete Shift:=xlToLeft
Columns("A:A").ClearContents
ActiveSheet.Shapes("ComboBox1").Delete
Application.GoTo Range("A1"), True
ActiveSheet.UsedRange
ActiveSheet.PageSetup.PrintArea = ""
ActiveWindow.View = xlNormalView
Application.ScreenUpdating = True
The original document is set as page break preview, which is why I change it to normal at the end of the code.
Even stranger, is that if I step through the code, this does not happen. Any idea's as to the cause?
Nathan
There's no place like home.....
There's no place like home.....
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Cells.Columns.Ungroup
But, when I add a saveas command, after closing and reopening, all is well.
Odd behaviour, but hay-ho.
Odd behaviour, but hay-ho.
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cells.Columns.Ungroup
Try moving the line
ActiveWindow.View = xlNormalView
to below the line
Application.ScreenUpdating = True
BTW the line
ActiveSheet.UsedRange
doesn't do anything.
ActiveWindow.View = xlNormalView
to below the line
Application.ScreenUpdating = True
BTW the line
ActiveSheet.UsedRange
doesn't do anything.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Cells.Columns.Ungroup
OM-Goodness - I swear I was going to give that same advice, but thought it to be too simple to be worth posting! If it works...I won't doubt myself again!
I'm holding thumbs that moving ActiveWindow.View = xlNormalView will work VegasNath !!!!!
I'm holding thumbs that moving ActiveWindow.View = xlNormalView will work VegasNath !!!!!
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Cells.Columns.Ungroup
To Rudi - Speaking for myself only... please always post - even if it seems too simple or intuitively obvious. There is no limit to my own ability to miss the obvious and often to not understand explanations others would think simple. Again, speaking for myself only.
Don
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Cells.Columns.Ungroup
Thanks 4 the encouragement. (Point taken!)
So... did the moving of the code work???
So... did the moving of the code work???
Last edited by Rudi on 04 Apr 2010, 17:16, edited 1 time in total.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Cells.Columns.Ungroup
To Rudi: I was speaking from a philosophical perspective; I was not the original poster, hence, no code.
Don
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Cells.Columns.Ungroup
dasadler wrote:To Rudi: I was speaking from a philosophical perspective; I was not the original poster, hence, no code.
Sorry...did not notice? I guess I was just too interested to see if that change was gonna work!
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.