Define the name of range

mubi_masti

New member
Joined
Oct 8, 2011
Messages
14
Reaction score
0
Points
0
any body can help me to identify error in the following code.

I have a macro to select the particular number of cells in the sheet and after selection i want to give the name of selected range as "tblheading"

i got selected the range but unable to define the name. code run fine to select the cells but not defining name of range.



Code:
Sub test()
    Dim xlApp As Application
    Dim sSheet As String
    Dim rangevalue As Range
    Set xlApp = Application
    Dim wb As Workbook, ws As Worksheet
    
    Set wb = ActiveWorkbook
    Set ws = ActiveSheet
    sSheet = "Sheet1"
    On Error Resume Next
    With xlApp
        .Application.Union(.Sheets(sSheet).Rows("5:5").SpecialCells(xlCellTypeFormulas), _
        .Sheets(sSheet).Rows("5:5").SpecialCells(xlCellTypeConstants)).Select
    End With
    
    
    Set rangevalue = Range(Union(Sheets(sSheet).Rows("5:5").SpecialCells(xlCellTypeFormulas), _
        Sheets(sSheet).Rows("5:5").SpecialCells(xlCellTypeConstants)))
    

wb.Names.Add Name:="tblrecords", RefersTo:= _
                  "=" & rangevalue

End Sub
 
Last edited by a moderator:

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
223
Reaction score
2
Points
18
Location
UK
Excel Version(s)
2016
Change the last line to:
Code:
 rangevalue.Name = "tblrecords"
 
Top