Catenate Help

[wwhit]

I have the following code for a catenate:

Sub CatenateIt()
'replace A1:G2 below with your own range
'DEMO: concatenate A1 to G2
Call GetUserRange
ActiveCell.Value = Catenate(Range("A1:IU1"), " ")

End Sub



'<< Function to concatenate cells in a range >>
Public Function Catenate(MyRange As Range, _
Optional Delimiter As String) As String
Dim Cell As Range, N As Long
N = 1
'go thru MyRange cell by cell and concatenate

For Each Cell In MyRange
If N = MyRange.Cells.Count Then
'we don't need a delimiter after last cell

Else
'otherwise we do need a delimiter

End If
N = N + 1
Catenate = Catenate & "'" & Cell & "'" & ", "

Next Cell
Set Cell = Nothing
End Function


And also this code that is called from the above:

Sub GetUserRange()
Dim UserEntry As String
UserEntry = InputBox("Select cell column for the output - Like A1:G1.", "Select Range.")
If UserEntry <> " " Then ActiveCell.Value = UserEntry

End Sub


I would like to know how to get the ranges in the Sub CatenateIt to be the UserEntry from GetUserRange InputBox. So instead of
ActiveCell.Value = Catenate(Range("A1:IU1"), " ")

I would like it to have something like this:
ActiveCell.Value = Catenate(Range("UserEntry"), " ")

So if the UserEntry is A1:G1 I would like it to pull that information. Any ideas?

[Bob Phillips]

Isn't it just


ActiveCell.Value = Catenate(UserEntry, " ")

[wwhit]

No, I have already tried that. It must be something else or it can not do this.

[wwhit]

Your reply helped alot. Never thought about it but I was trying different things and this is it. Thanks for your help.

ActiveCell.Value = Catenate(Range(UserEntry), " ")