Name a range in VBA (should be easy) - Page 2

Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: Name a range in VBA (should be easy)

  1. #11
    New Member
    Join Date
    Jan 2013
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

     
    Quote Originally Posted by jobohm View Post
    In my experience usedrange is less error-prone.
    Code:
    UsedRange.Rows(39).Name = "REPS"

    Thanks, but not looking to include columns A:B in this case.

    The code I used has worked for everything I've needed so far!

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

  2. #12
    New Member
    Join Date
    Jan 2013
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by nuked View Post
    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)
    Not sure what a dynamic named range is sorry... but yes it must be programmed for this case.

    Got the problem sorted anyway thanks

  3. #13
    New Member
    Join Date
    Apr 2014
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    [QUOTE=Camlamb28;3605085]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)

    Complicate but works:
    Range("B3:" & Left(Range("B3").End(xlToRight).Address(ColumnAbsolute:=False), InStr(Range("B3").End(xlToRight).Address(ColumnAbsolute:=False), "$") - 1) & Range("B3").End(xlDown).Row).Select

    Of course you can replace '.Select' with '.Name = "REPS"'

    Range("B3").CurrentRegion.Name = "REPS" will also works but you must be careful because it will consider any region where B3 belongs (ie it could select cells to the left and up from B3

    Cheers
    Last edited by Smora; Apr 17th, 2014 at 05:46 PM.

  4. #14
    New Member
    Join Date
    Jul 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Camlamb28 View Post
    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"
    Generally, it is best to avoid using "Selection" programmatically. It is useful for interacting with the user (User to VBA or VBA to User) but if it is being used "within the code" there is almost always a better way to execute an action. In this case, this code can be shortened to:

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

  5. #15
    New Member
    Join Date
    Dec 2017
    Location
    Vancouver, BC, Canada
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

      
    Quote Originally Posted by jobohm View Post
    In my experience usedrange is less error-prone.
    Code:
    UsedRange.Rows(39).Name = "REPS"
    UsedRange should work just fine.

    Code:
    UsedRange.ClearContents ' Clear data before pulling new data
    'Code to pull data
    UsedRange.Name="rData0" ' Name new used range as rData0

User Tag List

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