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

"TishyMouse" wrote:

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

mylastCol = cells.specialcells(xlLastcell).Column

--
Regards,
Tom Ogilvy




"TishyMouse" wrote:

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

"TishyMouse" wrote:

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

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.

"Tom Ogilvy" wrote:

mylastCol = cells.specialcells(xlLastcell).Column

--
Regards,
Tom Ogilvy




"TishyMouse" wrote:

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

"TishyMouse" wrote:

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

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.

--
Regards,
Tom Ogilvy


"TishyMouse" wrote:

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.

"Tom Ogilvy" wrote:

mylastCol = cells.specialcells(xlLastcell).Column

--
Regards,
Tom Ogilvy




"TishyMouse" wrote:

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

"TishyMouse" wrote:

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