Using the range object (Error 424)

[RobH1979]

I have a combo box whose source is a named range ("variablelist"). However I need to update the named range every time new data is input into the spreadsheet.

I have therefore written the following code to update the named range. However when I run it I get 'ERROR 424 - Object Required 'on the SET statement. I have MS DAO 3.6 Object Library referenced. Does anyone know what is causing this error?

Dim rngVariable As Range
Dim strSet as String

strset = "Settings"

Sheets(strSet).Select
Range("A2").Select
Set rngVariable = Range(Selection, Selection.End(xlDown)).Select

ActiveWorkbook.Names.Add Name:="VariableList", RefersTo:=rngVariable

 

[RoryA]

Remove the .Select from the end of that line.

 

[Colin Legg]

Hi,

Remove the select method from this line:

Set rngVariable = Range(Selection, Selection.End(xlDown)) .Select

When we are working with ranges from VBA it is very rare that we actually need to select them. In fact, selecting ranges can cause problems and slow the code down, so we try to avoid selecting when we can! This code should produce the same result without selecting:

Sub test()
    Dim rngVariable As Range
 
    With Sheets("Settings")
        Set rngVariable = .Range("A2", .Range("A2").End(xlDown))
    End With
 
    ThisWorkbook.Names.Add Name:="VariableList", RefersTo:=rngVariable
End Sub

Hope that helps

 

[RobH1979]

Cheers. Would have got it myself if the error message had been remotely useful!

 

[RoryA]

Or even:

Sub test()
    With Sheets("Settings")
        .Range("A2", .Range("A2").End(xlDown)).Name = "VariableList"
    End With
End Sub