Results 1 to 2 of 2

Thread: Define the name of range

  1. #1

    Define the name of range



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 Zack Barresse; 2011-10-25 at 05:00 PM. Reason: Added CODE tags

  2. #2
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    183
    Articles
    0
    Excel Version
    2010
    Change the last line to:
    Code:
     rangevalue.Name = "tblrecords"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •