Page 1 of 2 12 LastLast
Results 1 to 10 of 13
Like Tree1Likes

Name a range in VBA (should be easy)

This is a discussion on Name a range in VBA (should be easy) within the Excel Questions forums, part of the Question Forums category; Ok... I have a query that refreshes into an excel worksheet and want to Name the output which will always ...

  1. #1
    Board Regular WillR's Avatar
    Join Date
    Feb 2002
    Location
    Sutton Coldfield
    Posts
    1,143

    Default

    Ok... I have a query that refreshes into an excel worksheet and want to Name the output which will always be from cell A3 to cell D"something" (i.e. D 'end xldown??')

    What would be the VB code to select the output and Name the whole datatable???

    I'm sure this is an easy one.... for someone.

    Thanks,
    /**\ Regards, Will /**\

  2. #2
    Board Regular
    Join Date
    Aug 2002
    Location
    Newcastle
    Posts
    382

    Default

    You select the range that you want
    range("a1:A2").select

    and then use range.name = "name" to name the range.

    To name the whole application (is this what you want?) you can use

    Application.Caption = "Name"

  3. #3
    dk
    dk is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,934

    Default

    Alternatively,

    Range("A3:D" & Range("D3").End(xlDown).Row).Name = "DataTable"



  4. #4
    Board Regular WillR's Avatar
    Join Date
    Feb 2002
    Location
    Sutton Coldfield
    Posts
    1,143

    Default

    On 2002-10-30 07:40, k209310 wrote:
    You select the range that you want
    range("a1:A2").select

    and then use range.name = "name" to name the range.

    To name the whole application (is this what you want?) you can use

    Application.Caption = "Name"
    I think you missed my point. The range will potentially change each time the data is imported. I wish to, using VB, not manually, firstly determine the extent of the range (i.e. A3:Dwhatever) and then name it, as part of the data refresh procedure.

    I know i can use the following code...

    Code:
    Names.Add Name:="Groups", RefersTo:="
    But its the refers to bit i'm trying to ascertain, .....



    /**\ Regards, Will /**\

  5. #5
    Board Regular WillR's Avatar
    Join Date
    Feb 2002
    Location
    Sutton Coldfield
    Posts
    1,143

    Default

    On 2002-10-30 07:44, dk wrote:
    Alternatively,

    Range("A3:D" & Range("D3").End(xlDown).Row).Name = "DataTable"


    Ta Dan.... just the ticket!
    /**\ Regards, Will /**\

  6. #6
    New Member
    Join Date
    Dec 2011
    Location
    Norway
    Posts
    4

    Default Re: Name a range in VBA (should be easy)

    I often solve this with usedrange

    SheetCodeName.usedrange

    If I need to get around headers then try this :
    SheetCodeName.usedrange.offset(1,1).Resize(sheetCodeNAme.usedrange.rows.count -1, SheetCodeName.usedrange.columns.count - 1)

    If this range expands continously thats all you need, but usually it shrinks every now and then too. If so you'll need to .delete empty cells.
    Perhaps even .EntireRow.delete to avoid selecting a lot of useless empty cells.
    Last edited by jobohm; Aug 7th, 2013 at 05:30 PM.

  7. #7
    New Member
    Join Date
    Jan 2013
    Posts
    9

    Default Re: Name a range in VBA (should be easy)

    Quote Originally Posted by dk View Post
    Alternatively,

    Range("A3:D" & Range("D3").End(xlDown).Row).Name = "DataTable"
    Hey all,

    This is exactly the thing I'm trying to do, except take a range which goes to the right instead of downwards.

    I tried:

    Code:
    Range("C39:" & Range("C39").End(xlRight).Column & "39").Name = "REPS"
    But came up with error 1004 (Application or object defined error)


    Alternatively, I do have variables named in VBA for the number of rows across it will go if this is easier...


    Cheers,
    Cam

  8. #8
    New Member
    Join Date
    Jan 2013
    Posts
    9

    Default Re: Name a range in VBA (should be easy)

    Quote Originally Posted by Camlamb28 View Post
    Hey all,

    This is exactly the thing I'm trying to do, except take a range which goes to the right instead of downwards.

    I tried:

    Code:
    Range("C39:" & Range("C39").End(xlRight).Column & "39").Name = "REPS"
    But came up with error 1004 (Application or object defined error)


    Alternatively, I do have variables named in VBA for the number of rows across it will go if this is easier...


    Cheers,
    Cam

    Just had a brainwave of how to do this... And it worked! For reference for anyone else the code I used selected the range and then proceeded to name it.

    Here's the code:

    Code:
    Range("C39").Select
        Range(Selection, Selection.End(xlToRight)).Select
        
        Selection.Name = "REPS"

  9. #9
    New Member
    Join Date
    Dec 2011
    Location
    Norway
    Posts
    4

    Default Re: Name a range in VBA (should be easy)

    Quote Originally Posted by Camlamb28 View Post
    Code:
    Range("C39:" & Range("C39").End(xlRight).Column & "39").Name = "REPS"
    In my experience usedrange is less error-prone.
    Code:
    UsedRange.Rows(39).Name = "REPS"
    Last edited by jobohm; Oct 16th, 2013 at 09:58 AM. Reason: MyBad

  10. #10
    Board Regular nuked's Avatar
    Join Date
    Mar 2013
    Location
    London, UK
    Posts
    883

    Default Re: Name a range in VBA (should be easy)

    Must you do this programmatically? You could manually add a dynamic named-range:-
    Code:
    =OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A$3:$A$1048576),4)
    jobohm likes this.
    ****************************
    I'm good with Excel. I'm not great at:-

    (1) Guessing what solution you want to a problem
    (2) Guessing what your code might be
    (3) Math(s)
    (4) Physics
    (5) Creating systems that attempt to find patterns in lotteries
    (6) Hardware problems
    (7) Responding to questions marked "URGENT!!!!"
    (8) Predicting, at the start, what your future requirements might be - please include your whole requirements at the start

    Thanks. HTH
    ****************************

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com