VBA to bypass protected cell (Error #1004)?

[EssKayKay]

Hello,

I’m trying to find a VBA routine that will move down a column of values until it reaches the first blank cell. I tried running

Sheets("Amortize").Select
ActiveSheet.Unprotect
Range("M32").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1).Select

The problem is if a cell is populated it is also locked/protected. Therefore, when the active cell becomes the last populated cell before the “ActiveCell.Offset(1).Select” command I receive an Error 1004 stating “The cell is protected and therefore read only. . .”. I was hoping adding the ActiveSheet.Unprotect would resolve this but did not. Any suggestions would be greatly appreciated.

Thanks,
Steve K.

 

[EdNerd]

Caution: I am not an expert by any means. I'm sure there's a better way to do this.
But this worked for me:

Sub ScratchSelectCell()

Dim wks As Worksheet
Dim rw As Long

Set wks = ActiveSheet
wks.Range("A6").Select
rw = Selection.End(xlDown).row
wks.Range("A" & rw + 1).Select

End Sub

I find it much easier to work with the objects, rather than Selection. So I set the Worksheet object and referenced that in my Range > Select. I've had Excel get mixed up as to which sheet I wanted to find and select my range. Also, if I simply get the row number of the last populated cell, then set my next selection to "row + 1", there's not a problem of trying to select a locked cell. To be honest, though, I ran this on a column of numbers with all cells locked and protection on for all filled cells, and had no problems selecting the cell using VBA without removing protection. In your cell protection, did you accidentally check to not allow selecting unlocked cells?

 

[EssKayKay]

Caution: I am not an expert by any means. I'm sure there's a better way to do this.
But this worked for me:

Sub ScratchSelectCell()

Dim wks As Worksheet
Dim rw As Long

Set wks = ActiveSheet
wks.Range("A6").Select
rw = Selection.End(xlDown).row
wks.Range("A" & rw + 1).Select

End Sub

I find it much easier to work with the objects, rather than Selection. So I set the Worksheet object and referenced that in my Range > Select. I've had Excel get mixed up as to which sheet I wanted to find and select my range. Also, if I simply get the row number of the last populated cell, then set my next selection to "row + 1", there's not a problem of trying to select a locked cell. To be honest, though, I ran this on a column of numbers with all cells locked and protection on for all filled cells, and had no problems selecting the cell using VBA without removing protection. In your cell protection, did you accidentally check to not allow selecting unlocked cells?

Thanks Ed. This looks promising. However, I ran into another issue. I'll keep you posted.

 

[Michael M]

also, but what you very rarely need to .Select to do work with it.

Sub MM1()
Range("A6:A" & Selection.End(xlDown).Row + 1).Select
End Sub

 

[EssKayKay]

Thank you both for your suggestions and support - much appreciated.
Michael - I'm not quite sure where or how your MM1() code would fit into or modify my routine.
Following is what I have working.

Sub NextPayDate()
Dim wks As Worksheet
Dim rw As Long
Set wks = ActiveSheet
wks.Range("M32").Select
rw = Selection.End(xlDown).Row
wks.Range("M" & rw + 1).Select
End Sub

Again, my sincere thanks. . .
Steve