Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

How To Hide Rows In A Protected Sheet

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

1. How to hide rows in a protected worksheet without unprotecting the
2. Is it possible to delete rows in a protected worksheet without
unprotecting the worksheet.
3. My requirements is that in a worksheet, I have input certain formulas
and the same is protected with a password, I will be sending these sheets to
my colleagues to fill in data and the formulas will automatically calculate
the results. I dont want them to modify the protected ranges.

This protection will be for certain columns for the entire worksheet. In
case if they print then there are totals at the end of the sheet which also
has to be printed but I dont want empty rows without data not to be printed.

My querry is that is it possible to hide or delete empty rows in a protected
sheet without unprotecting the same.

4. These sheets will be used regularly as templates is it possible for them
to store the same in one file with different worksheet. In other words they
will copy and paste the template in the same file in different sheet but I
need the protection to continue is it possible.

5. Is it possible for someone to unprotect the sheet without the owner
disclosing the password. In other words is it possible to break the password.

Expecting your immediate response on the same.



View Answers     

Similar Excel Tutorials

Hide or Unhide a Worksheet by Hand in Excel
I'll show you how to hide worksheets in Excel so that a user cannot see them but you can still access data on them ...
Hide Data Within a Worksheet in Excel
In Excel you can actually hide data that is stored within a worksheet. This allows you to show data that is useful ...
Allow Only Certain People to Edit Specific Cells in Excel
How to allow only certain people to edit certain cells or ranges in Excel. This is a security feature that you can ...
Hide Formulas in a Worksheet and Prevent Deletion
This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protect ...

Helpful Excel Macros

Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Protect and Unprotect All Worksheets at Once
- This will allow you to protect or unprotct all worksheets in a workbook at once. It doesn't allow you to input a passwor
Delete Hidden Rows in a Workbook
- This macro will delete hidden rows from every worksheet in an entire workbook. However, note that this will only delete
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat

Similar Topics

Here's my dilemma;

I have a workbook which has a hidden worksheet where a password resides. My macro goes to this worksheet, collects the password and comes back to the current worksheet, unprotects it (with that password), imports data, and then reprotects the worksheet. Because the workbook is also protected, the sheet with the hidden password cannot be accessed.

Now I want the workbook to be "shared and protected" with "Track Changes". This will prevent the macro itself from being tampered with and will enable an audit trail of sorts on a 3rd worksheet within the workbook.

However, if the workbook is shared and protected, one needs to unshare and unprotect it first before unprotecting the worksheet that I need to import into. Sooooooooo,

I need to figure out how to (in VB) have the macro first unprotect and unshare the workbook (using the hidden password), then unprotect the worksheet (got that part figured out already), then reprotect the worksheet and the shared workbook. Any ideas on how I would do that?

I can send the current macro to those who wish to investigate.

Is there another way to protect the macros from being edited by someone with rights to the workbook?

Thanks in advance

The worksheet is already passord protected. I have assorted macros created to unlock the sheet with the password (without any user interface), perform a procedure and then reprotect the sheet. On the payroll portion of the worksheet, if the rows have been hidden, what is the code to require the user to provide a password that will unhide those rows (without unprotecting the entire sheet)?


I have a workbook that has several protected sheets, several password protected sheet, and several non-protected sheets.

I've looked all over, and cannot seem to find code that tests for the password protection status of a sheet (i.e. not just protected but password protected).

I've found a lot of information on the Workbook HasPassword property, but nothing at Worksheets level.

Is there such a code for this test, and/or is there another way of finding out?

Thanks in advance.


I really do need some help. I finished creating a Family budget plan on one
one entire sheet and at the end of it all I protected the worksheet and
workbook after completion. then and only then, I realized that there are
areas that i have that is protected that is NOT to be protected. Know when I
try to go in and unprotect the sheet/book it will not allow me to do so. the
option to unprotect is grayed out preventing me from unprotecting me
worksheet/book. if it helps, i am using office XP.

Hi Guys,

I am using Excel 2000. I am attempting to write a macro that hides rows on a protected worksheet.

This is how I am hiding a row.

Sheets("Sheet1").Range("A10:A49").EntireRow.Hidden = True

Everything was working great until I protected the sheet. Now I get an error. Is it possible hide rows on a protected worksheet?

Thanks for your help!

Hi all

I have a spreadsheet which I have password protected, in this sheet I also have some cells which are grouped (so there is a plus and minus button to show/hide options) but the problem is as the sheet is protected the user cannot use the groupings to show/hide the cells.

Is there a way of doing this in excel without using macros?

Thanks for any suggestions.


I'm at my wit's end trying to figure this one out. I'm working on a macro that opens Excel files and finds the last row. The purpose is to find files that were saved with unused rows causing wasted space. I've been using


to find the last row, which has been working, but it breaks when it reaches a protected sheet. Obviously I can unprotect the sheet in VBA, but the problem comes when it hits a protected sheet that has a password. It will then ask the user for the password (which is unknown), and when they hit cancel, the macro breaks.

So what I'm really looking for is one of three things:

1) Is there a way to find the last cell in a worksheet that works with protected cells?

Please don't suggest
Cells.Find("*", After:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _

or similar, as I've already tried it and it's not finding the true last cell. I believe the cells I'm looking for do not contain data or searchable formats.

2) Is there a way to find out if a worksheet is password protected?

I know there are ways to find if a worksheet is protected in general, but is there a way to see if there's a password associated with it? Note: I'm not looking for workbook protection, but worksheet protection. If this is the case, I can skip that specific sheet and move on.

3) Is there a way to get around the prompt for a password and skip the sheet/file?

I'd like to run this macro overnight (it searches a large number of files) and would like it to be able to skip over this problem without user input. So instead of fixing the break problem when some one hits Cancel, I would like it to skip the password prompt all together.

If any one knows how to do one of those three things, or has any other suggestions, I would greatly appreciate it.

Thank you in advance.

My workbook has a protected worksheet (including mostly locked cells); but I want to allow users to copy the worksheet (either by the worksheet "Move or Copy" function, or by clicking the upper-left corner to select all cells) to paste it into their workbooks (which I want no part of).

Is there a parameter I can set in the SheetX.Protect function to allow this flexibility?

The other options I can think of a
* unlock all cells to allow selecting and copying the entire worksheet;
* unprotecting the sheet for those specific users only (by VBA);
* giving those users the password;
* just unprotecting the sheet, period.


I have this code which allows me to Hide the formulas but i have a macro that needs to be run but this wont allow me to run the macro if the sheet is protected. What i wanted to do is have the sheet protected and password and formulas hidden the way it is but also have the option to enable me to run the macro without having to unhide the sheet/unprotect password etc.

Another thing i would like to do is have some sheets hidden (Not all the sheets but the the ones i want to hide) but also have them password protected by VBA so that its not a case where users can right click and Unhide sheet tabs but need the password to unhide

I am new to VBA.

Your help is much appreciated

Sub LockDown()
Dim WS As Worksheet
For Each WS In ThisWorkbook. WorkSheets
WS.Cells.FormulaHidden = True
WS.Protect Contents:=True, Password:="terces", UserInterfaceOnly:=True
ThisWorkbook.Protect Password:="drowssap", structu =True.
End Sub

I have a workboook that has three sheets.
Two of these are protected and hidden.
The third sheet is protected but has certain cells that can be amended by a third party. This third sheet also has a 'button' on it with a macro attached. Whilst in the protected mode, the macro will not run.

Is there any way around this without unprotecting the sheet?

I have realised that the macro is trying to adjust some of the protected cells so is the some code I can include in the macro that will unprotect the sheet and then re-protect it with the same password?

I need assistance developing vba code to unprotect a protected worksheet. Most tutorials concerning vba script and unprotecting sheets only refers to unprotecting an active sheet. I would like to have the vba script unprotect a specific sheet if possible. Please advise.

Ultimate Goal:
To prevent users of the worksheet from being able to input information into the raw data other than using the userform I've created.

The userform inputs information into the a sheet entitled "Details" and I'd like to have the "Details" sheet protected at all times. I would like the userform to be able to unprotect the "Details" sheet, input the data and protect the sheet when done.

How to set up a protected worksheet that is sortable? Is this even possible?
When I try, I get a warning popup from Excel saying:

The cell or chart you are trying to change is protected and therefore read-only.
To modify a protected cell or chart, first remove protection using the Unprotect Sheet command (Tools menu, Protection submenu). You may be prompted for a password.

Thanks in advance.

I'm using the Protect Sheet function in Excel to prevent editing of data in certain columns. I enabled the checkboxes to insert rows and delete rows., Whenever I try to insert a row it prompts me with the message- the cell or chart is protected and is read only. To modify the a protected cell, please remove protection for the sheet?

Before I protected the worksheet, I did a Ctrl A and made sure that all cells were unlocked. Then I went to the indivdual columns and selected Locked for the cells in that column?

Any thoughts why I am unable to insert or delete rows while the sheet is protected? Not sure if this matters, row A is merged cell spanning columns A:O



I have a sheet that upon opening a macro runs to hide a selection of columns based on cell containing certain words (see here).

I now also require the workbook to be protected so that the hidden columns (which Im sure are locked as default when hidden) are locked as are columns N & S. Now, I know I can do this by protecting the sheet and unprotecting the columns I require (ie N & S - with the hidden cells remaining locked anyway).

The issue is, every time the workbook is opened this protection must be in place.

However, I require a button, which could simply reside in A1, that when pressed, will unhide and unlocked all cells. - This is needed as its vital I can easily copy rows to another sheet (and delete from existing sheet) as and when required.

if possible, re-pressing the button would hide and proetct the worksheet however this is not too significant as long as when I close and open the workbook everything is hidden and protected as specified above.

There is no need to password protect anything.

Ive had a good search of the forums and have little bits of info from varuious threads. Any help would, as ever, be much appreciated.

Oh and the sheet in question is called 'Open Projects'.

Please help me.

I've locked certain columns on my worksheet so that users cannot overtype target dates etc. I've password protected the worksheet.

The password protection means that for some reason the users can't use the autofilters that are on the header row.

Can someone help me solve this problem; I still need the locked cells and password protection on the sheet but the success of the sheet depends on users being able to filter for specific rows using the autofilter....

Awaiting any responses.....

Hi all,

I have this excel files protected by password and I need to unprotect it so
I go to Tools -> Protection -> Unprotect Sheet to unprotect my worksheet.
The password was right and the process went on correctly without any error
or problem.

but the next time I open the same file again, I was prompted to key in
password again. and I had to use the same password to go in.

When I check on the Tools -> Protection. The unprotect sheet change to
Protect sheet. which means by right it should be an unprotected file. but
why I was prompt for password ?

I suspected it is due to corrupted file content. but how to fix it ? or it
is something else.

Any help is very much appreciated.

Thanks and Regards,

Bernard Goh
Success Electronics & Transformer Manufacturer Sdn Bhd

Morning All,

I have some formulas which are protected, so Ive put a password on the sheet. Problem is that I want the user to be able to delete rows... when I try and delete a row with the protection on I get the following error.

You are trying to delete a row that contains a locked cell. Locked cells cannot be deleted while the worksheet is protected.

Is there any code I can use to get around this? My protected columns are A and some of D.


Using Custom Views in a protected worksheet, the column hiding/unhiding implicit in the views does not occur. The following error message is displayed:
Some view settings could not be applied In the Add View dialog box, I've checked the "Hidden rows, columns and filter settings" option.

In the protection dialog box, I've checked the checkbox for "Format Columns", which I believe indicates that hiding/unhiding columns is permitted when the sheet is protected. And, when the sheet is protected, I can manually hide and unhide columns, but I want that to happen automatically when I select the custom view.

If I unprotect the worksheet, column hiding/unhiding occurs automatically when I select the custom view.

Can I write code to make the column hiding and unhiding occur when the view is selected, perhaps by unprotecting the sheet temporarily and then protecting it again once the selected view has been displayed?

We have a large (4,000+ rows) excel worksheet from a prior employee that I
would like to be able to use, but it is password protected and no one has the
password. Is there a way to copy or save it as a different file that will
not copy the password protection? It would save me a couple days of work if
it is possible.


I have a protected worksheet (I have password to unprotect) where some rows are grouped. I want to have the entire sheet protected, but I want to allow users to group/ungroup rows as needed.... Any suggestions?


Hello Everyone. I am using Excel 2007 and I cannot get my table to allow filtering when my worksheet is password protected. I recorded a macro to unprotect the worksheet, sort the table, then protect the worksheet again. My problem is that when I protect the sheet again, I lose the ability to filter the table even though the macro includes the following code when it protects the sheet again.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True

By trying different things I have found that if I password protect the sheet BEFORE the above code, filtering still works but the page is no longer password protected. It is simply protected (without a password). If I password protect the sheet after the above code, the sheet is password protected, but then I cannot filter the table.

Is there some way to allow filtering AND to have the sheet password protected?


Question: If I have password-protected a sheet but still want users to be able to highlight and hide rows on that sheet, how would I do that? Currently, with the password protection, they are unable to highlight a series of rows to hide.

Thank you for your response in advance.

Thanks to a post from Ron de Bruin I found the following to disable the Tools
> Protection option from the tool bar:

Application.CommandBars("Worksheet Menu Bar").FindControl _
(ID:=30029, Recursive:=True).Enabled = False

My question is where do I insert this code? Is it in the "This Workbook"
portion of the code? I gave that a try, but nothing happened.

I have protected all the cells where I do not want the user to change a
formula. However, if they try to change a formula they get the Excel message
box that advises them the cell is protected, then tells them how to go
unprotect the worksheet.

At first I password protected the sheet, but then my macro wouldn't run. I
was unsuccessful in writting code to unprotect, run the macro, then protect
the sheet. So, I decided to try just removing the tools>Protection option
from the menu so the user will not have any easy time unprotecting the sheet
and changing formulas.

If anyone has a better solution, please advise.


I have a spreadsheet with over a dozen sheets in it. The sheets are protected, but some of the cells on the sheets are unprotected for user input. Each sheet is protected with a password. Some users have access to that password and are allowed to unprotect a sheet and change a formula in the protected areas of the sheet. The owner of the spreadsheet wants to be able to track the changes the users make to the formulas in the protected cells. One thing that also makes this difficult is that rows can be inserted into the sheets by built in functions in the tool. Therefore, the formula that they changed may have a different row reference when rows are inserted. Anyone have any ideas on how this can be done?

I have a protected worksheet and want to allow the users to change the groupings (i.e. add or remove rows from the grouping) on this sheet.
I have read this post which was very helpful in allowing users to show and hide a group of rows. But it does not allow the user to add or remove rows to and from the group.

Would anyone be able to provide the additional VBA code to add this functionality.

Some more info on the particular file I am working on:
The worksheet I have has 4 sections which the users must fill out. Each section has 100 rows, 10 of which are visible. The other 90 are grouped and hidden. I want to allow the users to show as many rows as they need (for example 20). They must be able to remove rows from the group. The sheet is ofcourse protected.