help with "select method of range class failed"

[rhk001]

Hi there, I have a sub here with creates a string, I want to select the range but keep getting the following error "select method of range class failed"

the result of the string STR variable in this case is (str = =MyRangeSheet!$d:$d,MyRangeSheet!$e:$e)


Any ideas?

Thanks



Private Sub CreateColumnsRange(wrkbk As Workbook, _
wrksht As Worksheet, _
rangename As String, _
cols() As String)

'IN
' wrkbk = the workbook to which the range applies
' wrksht = the worksheet to which the range applies
' rangename = the name of the range
' cols = an array of column letters that will be the range
'

Dim ub As Long
Dim str As String
Dim i As Long

ub = UBound(cols, 1)
'RefersTo:="=Sheet1!$A:$A,Sheet1!$C:$C" <- col A and col C are the range
str = "=" & wrksht.Name & "!" & "$" & cols(1) & ":" & "$" & cols(1)
For i = 2 To ub
str = str & "," & wrksht.Name & "!" & "$" & cols(i) & ":" & "$" & cols(i) 'loop, adding the comma
Debug.Print "i= "; i; " str = "; str
Next i

wrkbk.Names.Add Name:=rangename, RefersTo:=str
Range(str).Select


End Sub

[Bob Phillips]

See if this helps



Sub CreateColumnsRange(wrkbk As Workbook, _
wrksht As Worksheet, _
rangename As String, _
cols() As String)

'IN
' wrkbk = the workbook to which the range applies
' wrksht = the worksheet to which the range applies
' rangename = the name of the range
' cols = an array of column letters that will be the range
'

Dim ub As Long
Dim str As String
Dim i As Long

ub = UBound(cols, 1)
'RefersTo:="=Sheet1!$A:$A,Sheet1!$C:$C" <- col A and col C are the range
str = "=" & wrksht.Name & "!" & "$" & cols(1) & ":" & "$" & cols(1)
For i = 2 To ub
str = str & "," & wrksht.Name & "!" & "$" & cols(i) & ":" & "$" & cols(i) 'loop, adding the comma
Debug.Print "i= "; i; " str = "; str
Next i

wrkbk.Names.Add Name:=rangename, RefersTo:=str
Range(str).Parent.Activate
Range(str).Select


End Sub

[rhk001]

Brilliant that seems to have done the trick I am not familiar with the Parent.Activate bit so with do some research.

Have a great day.

[Bob Phillips]

All it is doing is making sure that the sheet that that range is on is selected before trying to select the range.