Selecting a used cell in VBA

[pells]

Is it possible to select a used cell ie one that is not blank and ingore all others in a cell range?

For example, if B11 is not blank, then remove the cell protection across the row, otherwise do nothing.

Many thanks.

 

[pells]

Is it possible to select a used cell ie one that is not blank and ingore all others in a cell range?

For example, if B11 is not blank, then remove the cell protection across the row, otherwise do nothing.

Many thanks.

I need to be able to do this in a module, not a sheet module as I am planning to press a button to apply this to the cells manually.

I hope this makes sense?

Thanks.

 

[VoG]

Maybe like this

Sub test()
ActiveSheet.Unprotect Password:="password"
With Columns("B").SpecialCells(xlCellTypeConstants)
    .EntireRow.Cells.Locked = False
End With
ActiveSheet.Protect Password:="password"
End Sub

 

[pells]

Maybe like this

Sub test()
ActiveSheet.Unprotect Password:="password"
With Columns("B").SpecialCells(xlCellTypeConstants)
    .EntireRow.Cells.Locked = False
End With
ActiveSheet.Protect Password:="password"
End Sub

I have tried this, but the cells in column B that are blank are not protected, which I need them to be.

 

[VoG]

They must have been unlocked to start with. Try

Sub test2()
ActiveSheet.Unprotect Password:="password"
With Columns("B").SpecialCells(xlCellTypeConstants)
    .EntireRow.Cells.Locked = False
End With
With Columns("B").SpecialCells(xlCellTypeBlanks)
    .EntireRow.Cells.Locked = True
End With
ActiveSheet.Protect Password:="password"
End Sub

 

[pells]

They must have been unlocked to start with. Try

Sub test2()
ActiveSheet.Unprotect Password:="password"
With Columns("B").SpecialCells(xlCellTypeConstants)
    .EntireRow.Cells.Locked = False
End With
With Columns("B").SpecialCells(xlCellTypeBlanks)
    .EntireRow.Cells.Locked = True
End With
ActiveSheet.Protect Password:="password"
End Sub

Many thanks for this, but its not quite what I am looking for.

I wil try to explain a little better and hopefully this time will make sense.

I have 5 sheets with a copy button on each of the sheets. When the button is pressed, the user enters in the sheet name to copy to - this works fine, but the copy, copies all of the sheet from range B11 to T81.

What I have on my sheets is protection and unfortunately the protection is overwritten when I copy from say sheet1 to sheet2 and was hoping only to copy the contents from sheet1 if there is something in cell range B11 to B81, then copy the whole roll, including blanks. This would mean that the protection will remain in the rows that are blank.

I hope this makes sense and you can help me?