Hey there,
I found some code online that works great for defining a named range. My only issue is that occasionally my worksheet name will contain spaces. this throw off the current code. i do know that if i manually go in to the range and add a "'" to the beginning and to the end of the worksheet name it fixes it. unfortunatly i cannot figure out how to add the "'" in the following VBA.
thanks
I found some code online that works great for defining a named range. My only issue is that occasionally my worksheet name will contain spaces. this throw off the current code. i do know that if i manually go in to the range and add a "'" to the beginning and to the end of the worksheet name it fixes it. unfortunatly i cannot figure out how to add the "'" in the following VBA.
thanks
Code:
Sub AddDynamicRangeVertical()
On Error Resume Next
Dim sRangeName As String
Dim n As Name
If ActiveWorkbook Is Nothing Then Exit Sub
sRangeName = InputBox("Enter a range name, then push OK. ", _
"Add Vertical Dynamic Range")
If sRangeName = "" Then Exit Sub
sRangeName = Replace(sRangeName, " ", "_")
ActiveWorkbook.Names.Add Name:=sRangeName, _
RefersTo:="=OFFSET(" & ActiveSheet.Name & "!" _
& ActiveCell.Address & ",,,COUNTA(" & ActiveSheet.Name _
& "!" & Columns(ActiveCell.Column).Address & "))"
For Each n In ActiveWorkbook.Names
If n.Name = sRangeName Then Exit Sub
Next n
MsgBox Err.Description, , "Invalid Name"
On Error GoTo 0
End Sub