VBA: Create Multiple Named Ranges

[mrMozambique]

Hi all. I'm having trouble with some code to create a series of named ranges (one in each column for an indeterminate number of columns). It seems to be adding an unnecessary set of quotation marks (e.g. ="'tables'!$BP$4:$BP$101" instead of ='tables'!$BP$4:$BP$101). How can I remove the quotation marks? Thanks in advance for any help you can provide.

Sub MakeNamedRanges()

  Dim C As Long
  Dim Header As String
  Dim LastCol As Long
  Dim RefStr As String
  Dim Rng As Range
  Dim RngEnd As Range
  Dim Wks As Worksheet
  
    Set Wks = ActiveSheet
    
      LastCol = Wks.Cells(1, Columns.Count).End(xlToLeft).Column
        For C = 3 To LastCol
          Set Rng = Cells(4, C)
          Set RngEnd = Wks.Cells(Rows.Count, C).End(xlUp)
          Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Wks.Range(Rng, RngEnd))
            RefStr = "'" & Wks.Name & "'!" & Rng.Address
            Header = Wks.Cells(3, C)
            On Error Resume Next
              Wks.Parent.Names(Header).Delete
              Err.Clear
            On Error GoTo 0
            Wks.Parent.Names.Add Header, RefStr
        Next C
        
End Sub

 

[AlphaFrog]

Try this...

Wks.Parent.Names.Add Header, [COLOR="Red"]"=" & [/COLOR]RefStr

Also, you don't need this.

           On Error Resume Next
              Wks.Parent.Names(Header).Delete
              Err.Clear
            On Error GoTo 0

The .Add method in this case will overwrite the Name if it already exists.

 

[mikerickson]

Or

For C = 3 To LastCol
    Set Rng = Cells(4, C)
    Set RngEnd = Wks.Cells(Rows.Count, C).End(xlUp)
    Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Wks.Range(Rng, RngEnd))
    Header = CStr(Wks.Cells(3, C))
    Rng.Name = Header
Next C

 

[mrMozambique]

MrExcel saves me again. You guys are stars. Many thanks!