ActiveSheet.ShowAllData failing- 1004

[beckyinDC]

ActiveSheet.ShowAllData failing - 1004

the worksheet ("TPRs").activate line is right before and seems to work because I can see the activesheet object in watch window.

the TPRs worksheet has 1 list object and 1 pivottable whose cache is the list the listobject on the same worksheet.

the sheet is unprotected.

other worksheets that have pivottables and charts based upon the cache from the list object on the TPRs worksheet are also unprotected.

I cn manually select all cells on the TPRs sheet and rightclick and unhide. If I record that action I get
Cells.Select
Selection.EntireRow.Hidden = False

Is that not the same thing? Would rather use ShowAllData, but guess I could go with that...

I am using excell 2007- any thoughts why the basic command isnt working for me? thank you!

 

[Jonmo1]

No, it's not the same thing..

ShowAllData refers to data that has been filtered from Data - Filter
This is NOT the same as Right Click - Hide

Instead of selecting, then using hidden = false, just do it in one line
Sheets("TPRs").Cells.EntireRow.Hidden = False

Hope that helps.

 

[Joe4]

Are you running this code from a standard module or a worksheet module?
If from a worksheet module, which worksheet?

 

[Jonmo1]

Further detail...

ShowAllData will effect rows that have been filtered from Data - Filter
It will NOT effect rows hidden manually by right click - hide

And the reverse is also True

EntireRow.Hidden = False will effect rows that have been hidden by Right Click - Hide
It will NOT effect rows that have been filtered from Data - Filter.

 

[beckyinDC]

Sorry for the delay...thank you both very much

I was calling ShowAllData from a regular module, not the worksheet.

I was really attempting to deal with those hidden rows, so based upon your responses, I think I can leave the ShowallData alone, though I still dont understand why it didnt want to work.

thanks for the hint about not selecting too-

Becky

 

[Jonmo1]

Hmm, after further looking it seems my last post was not entirely accurate...

It's correct that:
ShowAllData is a command used on a range that has been filtered from Data - Filter.
And
Right Click - Hide IS NOT the same as Data - Filter

But the reverse was not true..

EntireRow.Hidden = False will effect rows that have been hidden by Right Click - Hide
It will NOT effect rows that have been filtered from Data - Filter.

This was not entirely accurate..

EntireRow.Hidden = False WILL unhide rows that have been hidden by the autofilter.
But only consecutive rows...
Example
If Rows 2 3 and 4 are hidden via auto filter, but all others are NOT, then
Range("A2:A3").EntireRow.Hidden = False WILL work.
BUT
Range("A1:A7").EntireRow.Hidden = False will NOT work.
Because there is a mixture of hidden and unhidden rows.


Hope that helps clear it up a bit.