Find last row in a password protected sheet

[FortyTwo]

Hello,

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

ActiveCell.SpecialCells(xlLastCell).Row

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, _
SearchOrder:=xlByRows).Row

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.

 

[schielrn]

Welcome to the Board!

Maybe look into password protecting the sheet for userinterfaceonly.

Do a search for userinterfaceonly on the board or on google and there are many examples of how to set this up. It is pretty simple.

Hope that helps.

 

[Jonmo1]

Welcome to the board...

Try this way instead of using SpecialCells.

this will get the Last Used Row of Each Sheet. Even if that sheet is protected.

For Each ws In Sheets
    x = ws.UsedRange.Row + ws.UsedRange.Rows.Count - 1
    MsgBox "Sheet " & ws.Name & " Last Row Is " & x
Next ws

Hope this helps..

 

[FortyTwo]

Sorry, schielrn, I missed another part of the problem. The files I am searching through were not created by me. They were created by various users who set their own protection settings. I am not authorized to change them (if I were, this wouldn't be a problem ). That's why I need a workaround.

Thanks jonmo1, I tested it on a small test set of files and it seems to work. I'll try it out on the larger directory and let you know how it works out. I appreciate the speedy replies.

 

[FortyTwo]

I just finished running the macro (it's a big file list). I'm going to do some spot checking on the results but based on the output it seems to have worked fine.

Thank you, jonmo1.

 

[Gardoglee]

A problem many run into with the .UsedRange.Rows.Count method is that formatted cells show in the UsedRange. If what you are asking for is the last row with actual data, value or formula in at least one cell, and someone has formatted a whole column, you will always get the maximum number of rows your version of Excel supports, not the last row with a value. Hence use of the Find "*" or use of SpecialCells(xlCellTypeLastCell), neither of which can be executed when the worksheet is protected.

 

[Fluff]

Not sure if you're aware, but this thread is over 10 years old!

Also you can use .Find on a protected sheet