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
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, _
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.