Hidden Rows

[Dan Morris]

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

 

[Joe4]

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?

 

[Dan Morris]

Hi Jo

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

thank you. Dan

 

[Joe4]

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.

 

[JoSal73]

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

 

[Fluff]

Another possibility, do you have autofilters on that file?

 

[Dan Morris]

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.

 

[Dan Morris]

No, there's no autofilters.
Dan

 

[Joe4]

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:

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.