Name a dynamic range - Run-time error 1004 Method 'Range' of object'_Worksheet' failed

Hello

Just written a simple (or so I thought!) macro to give a name to dynamic range but get the runtime error 1004 message in the thread title.

Code is as follows:

PHP Code: 

Sub RangeNameUpdate()
'
' RangeNameUpdate Macro
' Macro recorded 03/05/2012
'
Dim ws As Worksheet
Dim MyRange As Range

Set ws = Worksheets("LOOKUP")
    
    With Sheets("LOOKUP")
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Set MyRange = ws.Range("A3:A", ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
    End With
    ActiveWorkbook.Names("WORKSHEETS").Delete
    MyRange.Name = "worksheets"
    
End Sub 


Error appears on line starting "Set MyRange = "

Would be grateful for the fix!

Thanks

Please Login or Register  to view this content.

Should be:

Please Login or Register  to view this content.

Think of:

Please Login or Register  to view this content.

as a String, where you are adding a number to "A3:A"

Thank you both for the solutions and the tips