Dan Morris

New Member
Joined
May 22, 2012
Messages
37
Office Version
  1. 365
Platform
  1. Windows
Hi there

I wonder if someone could help me with a strange thing that's happening to a spreadsheet we use at work. I can't find anything in another thread about this.

The spreadsheet is a database of staff and lots of columns of related data such as dates.

It's used by 3 different people on a shared drive with a password to edit it. Sometimes rows are hidden - all 3 of us claim not to be hiding rows - at least not deliberately, so it's a mystery how this keeps happening.

Is there any way to make it impossible for anything in the spreadsheet to be hidden?

Thank you. Dan
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Event procedure VBA code can automatically hide rows.
Does the file have any VBA code in it?
What is the extension that this file uses?
 
Upvote 0
Hi Jo

No, not to my knowledge. The file extension is .xlsx

thank you. Dan
 
Upvote 0
If it has a "xlsx" extension, then that also implies that there is no VBA code.

Not sure how it could be happening, other than someone deliberately doing that. When this happens, can you see who last saved the file? Is it always the same person?
Here is how you can find that out: https://windowsinstructed.com/how-to-determine-who-last-modified-an-excel-file/

If you did want to add VBA, you could add code that runs upon opening the file that makes sure all rows are columns are unhidden.
 
Upvote 0
As Joe4 mentioned, VBA can auto hide rows, but it can also auto-unhide rows. Would it maybe be easier to just convert the file to xlsm and add some code to un-hide on workbook open event?

Jose
 
Upvote 0
Another possibility, do you have autofilters on that file?
 
Upvote 0
Hi there - that sounds like a great solution. If you could please advise me how to add VBA code so that it automatically un-hides any hidden rows or columns, that would be perfect.

Thank you.
 
Upvote 0
OK. Place this in the "ThisWorkbook" module in VBA for that workbook, and just update the sheet name to run it on in the code:
Code:
Private Sub Workbook_Open()

'   Indicate which sheet to run on
    Sheets("Sheet1").Activate
    
'   Unhide all rows and columns
    Cells.EntireColumn.Hidden = False
    Cells.EntireRow.Hidden = False
    
End Sub
Then, as long as they permit VBA code to run, it will automatically unhide all rows and columns upon opening the file.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

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