problem with 'xlDOWN'

[ajetrumpet]

Hello again folks. Having a bit of a problem with the 'END' property this time.

I have this in code:

    ActiveSheet.Unprotect
    Columns("A:AM").Hidden = False
    [COLOR="Red"]Range("A3", "AL3").End(xlDown).Locked = True[/COLOR]

    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
        :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
        AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True

The portion in red is not activating. However, I can get this to work:

Range("A3", "AL3").Locked = True

The cells in this range lock just fine, and the user cannot select them. The first range however, does not work.

Am I using the .END property incorrectly here?

 

[shades]

Howdy. I think when you use the End(xlDown) that you need to select the range first. With the Range, the selection is inherent in the argument. I could be wrong, I'm working only from memory.
________
Buy Vaporizers

 

[ajetrumpet]

can you clarify this for me a little bit Shades??? What do you mean by "Range is inherent in the argument"??

should I be writing it this way...???

    ActiveSheet.Unprotect
    Columns("A:AM").Hidden = False
    [COLOR="Red"]Range(("A3").End(xlDown), ("AL3").end(xldown)).Locked = True[/COLOR]

    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
        :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
        AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True

that just doesn't make a whole lot of sense!!

 

[shades]

No, no. Check to see whether the range is, in fact, being selected. So take this

Range("A3", "AL3").End(xlDown).Locked = True

and modify it to this. Try this and see what happens when you step through using F5 (helps to have Excel and VBA both visible, so arrange windows to check):

Range("A3", "AL3").End(xlDown).Select

Does this select the range you want?

(PS Unfortunately, my "retired" signature also means that I no longer have access to XL2003, so I can't test it for you. I work entirely on my MacBook Pro, and have not loaded Windows into Boot Camp)
________
Free Joomla Themes

 

[ajetrumpet]

Shades,

I did try that, and the selection of A3 to AL3 alone does highlight. The xldown part of it does not highlight the rows. I am at a loss here...

 

[shades]

At least now you know where to search for a solution.

I'm sorry I can't help you further because I no longer have access to XL and VBA. I will check two VBA books tonight to see what I can find for you.
________
Yamaha Cp300 Specifications

 

[ajetrumpet]

thanks. appreciate it! I was for sure that this would work, but I'm almost positive that the syntax is off slightly; I just can't figure out where....

 

[chergh]

Shouldn't it be

Range("A3:AL" & RANGE("AL3").end(xldown).row).locked = true