ActiveSheet.ShowAllData failing- 1004

beckyinDC

Board Regular
Joined
Mar 24, 2011
Messages
56
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!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.
 
Upvote 0
Are you running this code from a standard module or a worksheet module?
If from a worksheet module, which worksheet?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top