Can't Unhide columns - What the ???

[TAPS_MikeDion]

Hi all,
Has anyone ever run into this? I was working with Excel 2007 at home and hid all columns to the right of the last column in every sheet. Now that I'm at work using Excel 2003, the last visible column in each sheet has no border lines for each cell, which for whatever reason Excel won't allow me to change, and I cannot Unhide the hidden columns!

ANY help would be greatly appreciated.

Thanks,
Mike

 

[DaveU]

Hi Michael,

Dave
It may also be the code itself, and it's compatiblity with 03/10...just a thought !

You may be right, and I think I may have found the problem (kind of). The macro I described actually copies a sheet into a new workbook, and saves out the new workbook. So, the copy code goes like this:

r.PasteSpecial xlPasteAll
r.PasteSpecial xlPasteColumnWidths 'keep col width

It's the second line that's causing the problem ("r.PasteSpecial xlPasteColumnWidths") - with xl2003, if any columns are hidden, somehow the "hidden" property gets lost, and only the column width (0) is copied over. At least, that seems to be what's happening. With 2010, the hidden property gets copied across OK.

Strangely, when I run all this in a test workbook, with 2003, this code seems to work OK. So there must be something unique in my original workbook that's causing 2003 to stumble. I'll do some more testing tomorrow, it's waay past my bedtime!

regards,

Dave

 

[Michael M]

Has it only happened in this one workbook ?
The 2003 file might be corrupted

 

[DaveU]

Hi Michael,

Yes, that's what it was, one sheet in the workbook was corrupted. Copying the whole sheet to a new sheet didn't fix it, so I used a macro to copy it over cell by cell, and voila, problem disappeared! This was a subtle sort of corruption - if I hadn't been using 2003 I wouldn't have been aware that there even was a problem. And it was the most complicated sheet in the workbook, of course.

Thanks so much for your help Michael, and you too Tom, it got me looking in the right place for the trouble. Very much appreciated.

Regards,

Dave

 

[Michael M]

Glad we could help...thanks for the feedback..

 

[Semanon]

Hello all,

I'm using 2007 & have the same pblm.

I tried the solutions above & used a macro hoping it might work better than doing it through the gui...

Sub unhideColumns()'
ActiveWindow.SplitColumn = 0: Cells.Columns.Hidden = False: ActiveSheet.ScrollArea = ""
    Cells.Select
    Selection.EntireColumn.Hidden = False
    Range("A1").Select
End Sub

unfortunately, the columns are still hidden...

Any thoughts other than recreating a very complicated worksheet?

Thanks.

 

[Uli2001]

In my case (Excel 2008 for Mac), I just pressed Apple+A to select the entire spreadsheet, pressed Apple+C to copy, created a new Workbook, and pressed Apple+V to paste. All hidden columns became visible.

 

[mole999]

I found out yesterday that hidden comments that would display over cells when opened cause hiding issues, first time I have found an answer, I guess it could go the over way

 

[mole999]

The other though is 2003 is limited to 256 (IV) columns, later turned to 16 thousand (XFD)

 

[boostd]

Replying to add my resolution to this same issue:
I had this same issue of unhiding cell rows not functioning (Excel 2013). I was about at my wits end and realized I hadn't checked one of the most basic things: it turns out that there was a filter on the sheet.
In the "Home" tab under the "Editing" section I turned the filter off and all rows were visible again.

 

[Xavier Paradell]

Thanks Tom. This solved me the problem.