Dynamic Name Range - VBA

[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

 

[flexinau]

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

 

[Al Chara]

how can it be written in VBA where I want to name a range where the row number is variable.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Test()

    <SPAN style="color:#00007F">Dim</SPAN> intRow
    
    intRow = 2
    ActiveWorkbook.Names.Add Name:="rngNamed", RefersTo:="=" & Cells(intRow, 1).Address
        
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

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...

 

[flexinau]

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

 

[tusharm]

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.

 

[Aladin Akyurek]

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?

 

[flexinau]

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.

 

[tusharm]

For a programmatic solution, use

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

 

[mskeys2xx3]

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

Set myRange = Application.InputBox(prompt:="Enter K6:K" & LastRow, Type:=8)
    NameOfRange = "POC"
    
    ActiveSheet.Protection.AllowEditRanges.Add Title:=NameOfRange, Range:=myRange

 

[psyklone]

For a programmatic solution, use

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?