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