Cells.Columns.Ungroup

[VegasNath]

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?

[HansV]

Try

Cells.Columns.Hidden = False

[VegasNath]

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?

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

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?

[VegasNath]

But, when I add a saveas command, after closing and reopening, all is well.

Odd behaviour, but hay-ho.

[HansV]

Try moving the line

ActiveWindow.View = xlNormalView

to below the line

Application.ScreenUpdating = True

BTW the line

ActiveSheet.UsedRange

doesn't do anything.

[Rudi]

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 !!!!!

[dasadler]

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.

[Rudi]

Thanks 4 the encouragement. (Point taken!)

So... did the moving of the code work???

[dasadler]

To Rudi: I was speaking from a philosophical perspective; I was not the original poster, hence, no code.

[Rudi]

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!