Finding the last cell in a protected sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The command:

SpecialCells(xlLastCell).Select

fails if my worksheet is protected. Is there any way round this without
resorting to unprotecting the sheet (I don't want to have to hard code the
password in my macro)?

Thanks

TM
 
Oh it's OK, just found the answer in the archive :-) Duh!

myLastCol = _
..Cells.Find("*", After:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column

Thanks anyway

TM
 
THanks, Tom, but that is the command that I was trying originally doesn't
seem to work on a protected sheet.

The 'find' method works fine, it's just a bit longer.
 
Sub ABC()
ActiveSheet.Protect UserInterfaceOnly:=True
Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)
lastcol = rng.Column
MsgBox lastcol
End Sub

worked fine for me on a protected sheet. If it is password protected, in
xl2002 and later you would have to supply the password.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Determining last active cell in a protected worksheet 2
Copying in sheet with protection with the use of a macro 5
Protecting & Unportecting the sheet. 5
Protecting worksheet. 1
Spell Checking on a protected sheet 1
Automatic Password Protect Sheet 1
Controlling worksheet protection via VBA 0
Protecting sheet in a macro doesn't work 1

Back
Top