Select entire column in a dynamic named range, except the last row

[Jakejake]

Good morning all.

I'm trying to select a column (9th one) in my dynamic named range, and remove the cell protection from it.

I can do this via:

.columns(9).Locked = False

However, I wish to keep the very last row locked, but being dynamic I cannot specify an exact row. It moves up and down...

Is there a way I can do this, perhaps via Count or Offset?

Please help.

Thanks in advance

 

[Crystalyzer]

This should give you a head start on adjusting your code.

Sub UnlockColumn()
    Dim r1 As Range
    Dim r2 As Range
   
    Set r1 = Range("rng")
   
    Set r2 = r1.Columns(9)
   
    Set r2 = r2.Resize(r2.Rows.Count - 1)

    r2.Columns(1).Locked = False
  
End Sub

 

[AlphaFrog]

.Columns(9).Resize(.Rows.Count-1).Locked = False

 

[Crystalyzer]

.Columns(9).Resize(.Rows.Count-1).Locked = False

I took the loooooooong way around the fence I see! Thanks @AlphaFrog for showing me a more efficient way!

 

[Jakejake]

.Columns(9).Resize(.Rows.Count-1).Locked = False

Perfect, thank you.

Out of curiosity the resize command seems to resize the range I guess. So I could always adjust it to something like "third row up from the bottom" etc...? '-1' becomes '-3' I guess.

 

[AlphaFrog]

You're welcome and you are a good guesser.

 

[Jakejake]

You're welcome and you are a good guesser.

I guess !