VBA to define a range - worksheet name w spaces

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
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

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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

rjwebgraphix

Well-known Member
Joined
May 25, 2010
Messages
588
This may do the trick...

Code:
ActiveWorkbook.Names.Add Name:="'" & sRangeName & "'", _
    RefersTo:="=OFFSET(" & ActiveSheet.Name & "!" _
    & ActiveCell.Address & ",,,COUNTA(" & ActiveSheet.Name _
    & "!" & Columns(ActiveCell.Column).Address & "))"

Notice when adding the name you're inserting a ' with it. As you've figured out, VBA has issues with multi-word sheet names without it.
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
hmmmmmmm...getting an error. doesn't the ' have to be part of the " & ActiveSheet.Name & "!"? thanks
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
Try this.
Code:
ActiveWorkbook.Names.Add Name:=sRangeName, _
    RefersTo:="=OFFSET('" & ActiveSheet.Name & "'!" _
    & ActiveCell.Address & ",,,COUNTA('" & ActiveSheet.Name _
    & "'!" & Columns(ActiveCell.Column).Address & "))"
 

Watch MrExcel Video

Forum statistics

Threads
1,122,418
Messages
5,596,028
Members
414,039
Latest member
southike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top