Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Dynamic Name Range - VBA

  1. #1
    Board Regular
    Join Date
    Sep 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Dynamic Name Range - VBA

    Hi,

    Please, how can it be written in VBA where I want to name a range where the row number is variable. In my case, the row number should be the last row.

    ps The data is contained in a column.

    Thanks

  2. #2
    Board Regular
    Join Date
    Sep 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Name Range - VBA

    More info...

    By the way, I am naming a range through Insert/Name/Define.
    I then need to use this name when I do Data/Validation and I use a list.

    Ta

  3. #3
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Name Range - VBA

    Quote Originally Posted by flexinau
    how can it be written in VBA where I want to name a range where the row number is variable.
    Sub Test()

    Dim intRow

    intRow = 2
    ActiveWorkbook.Names.Add Name:="rngNamed", RefersTo:="=" & Cells(intRow, 1).Address

    End Sub



    Quote Originally Posted by flexinau
    In my case, the row number should be the last row.

    ps The data is contained in a column.
    I have to admit that I have no idea what you are talking about...
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  4. #4
    Board Regular
    Join Date
    Sep 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Name Range - VBA

    Sorry for being vague, I was in a bit of a hurry.. Here is the whole story.

    I am writing a macro that will change the source of a drop down combo box every time I run it. The source data is contained in one column. I have named the source data through Insert/Name/Define. The number of rows for the source data can change when I update it with new items. The source data could have 2, 10 or 20 rows.

    Now, the plan is to have a macro to update the Drop down combo box every time I update the source data so it will show all the items in the list.

    Beware my list may contain 1 blank row in between all the nonblank data.

    Hope this helps

  5. #5
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    11,007
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Dynamic Name Range - VBA

    You need to get rid off the blank row. Then, you don't need to update any thing through VBA, just use a named formula (Insert | Name > Define...) such as AcceptableList =OFFSET(Sheet1!$H$1,0,0,COUNTA(Sheet1!$H:$H),1)
    In this example, column H contains the acceptable values starting with H1. As values are added in column H, the formula named AcceptableList will adjust itself to include the new values.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Dynamic Name Range - VBA

    Quote Originally Posted by flexinau
    Hi,

    Please, how can it be written in VBA where I want to name a range where the row number is variable. In my case, the row number should be the last row.

    ps The data is contained in a column.

    Thanks
    What kind of data -- numeric or text and in which column?

  7. #7
    Board Regular
    Join Date
    Sep 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Name Range - VBA

    Thanks Tusharm. I'll try this out.

    Aladin, the source data for the drop down combo box is in column S for example. I will update the list in column S for every update. As far as whether the data is numeric or string, does it matter? because there are a number of columns involved and some are text and some are numeric.

  8. #8
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    11,007
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Dynamic Name Range - VBA

    For a programmatic solution, use
    Code:
    Option Explicit
    
    Function InUseColRange(RefCell As Range) As Range
        Dim TopCell As Range, LastCell As Range
        With RefCell.Parent
        Set TopCell = .Cells(1, RefCell.Column)
        If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)
        If TopCell.Address = .Cells(Rows.Count, RefCell.Column).Address Then
            Set InUseColRange = Nothing
        Else
            Set LastCell = .Cells(Rows.Count, RefCell.Column).End(xlUp)
            Set InUseColRange = Range(TopCell, LastCell)
            End If
            End With
        End Function
    Sub testit()
        On Error Resume Next
        MsgBox TypeName(InUseColRange(Range("a1"))): MsgBox InUseColRange(Range("a1")).Address
        MsgBox TypeName(InUseColRange(Range("s1"))): MsgBox InUseColRange(Range("s1")).Address
        End Sub

  9. #9
    New Member
    Join Date
    Dec 2009
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Name Range - VBA

    Can someone help me out with this?? It doesn't seem to be working. I also tried to use to use an input box and that didn't work either
    Code:
     
    Set myRange = Application.InputBox(prompt:="Enter K6:K" & LastRow, Type:=8)
        NameOfRange = "POC"
        
        ActiveSheet.Protection.AllowEditRanges.Add Title:=NameOfRange, Range:=myRange

  10. #10
    New Member
    Join Date
    Jun 2011
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Name Range - VBA

    Quote Originally Posted by tusharm View Post
    For a programmatic solution, use
    Code:
    Option Explicit
     
    Function InUseColRange(RefCell As Range) As Range
        Dim TopCell As Range, LastCell As Range
        With RefCell.Parent
        Set TopCell = .Cells(1, RefCell.Column)
        If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)
        If TopCell.Address = .Cells(Rows.Count, RefCell.Column).Address Then
            Set InUseColRange = Nothing
        Else
            Set LastCell = .Cells(Rows.Count, RefCell.Column).End(xlUp)
            Set InUseColRange = Range(TopCell, LastCell)
            End If
            End With
        End Function
    Sub testit()
        On Error Resume Next
        MsgBox TypeName(InUseColRange(Range("a1"))): MsgBox InUseColRange(Range("a1")).Address
        MsgBox TypeName(InUseColRange(Range("s1"))): MsgBox InUseColRange(Range("s1")).Address
        End Sub
    What do we change to make this work in other scenarios?
    I'm doing something similar, but mine stretches 20+ columns (static), can be from 1-20 rows, and is in the middle of the sheet.
    Can anyone provide an answer that isn't so vague for us newbs?

Some videos you may like

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
  •