Usedrange of a named range

[vijehspaul]

Hello Masters,

In my excel workbook, I have defined many named ranges. Most of these named ranges are dynamic in nature based on user inputs.
Is there any way to find and use 'usedrange' of a named range in a form?

I will explain:
range("A1:A10") named as say 'index'
The range will get updated dynamically. meaning sometime A1:A3 may have data, or A1:A9 may contain data.

There is a ComboBox in a form and wanted to set the .rawsource as usedrange of 'index'
so that the drop-down should show only cells that have data as list.

something like;

with ComboBox1
.rowsource= activeworkbook.names("index").usedrange
(couldn't find this syntax though.... )
end with

please advice.

 

[Luke M]

Why not make the Named Range itself dynamic?
If you made a range called "myData" with formula:
=A1:INDEX(A:A,COUNTA(A:A))

Then it would automatically adjust as you add/remove data from a1:a10. Then your combo box can refer to myData as the source range.

 

[Deepak]

Try something like this..

With Sheet1
    Me.ComboBox1.List = .Range("$H$1:$H" & Application.CountA(.Columns("H"))).Value

'or

'    Me.ComboBox1.List = .Range("H1").CurrentRegion.Resize(, 1).Value
End With

 

[NARAYANK991]

Hi ,

What you are looking for may be :

Me.ComboBox1.RowSource = ThisWorkbook.Names("Index").RefersTo

BTW , it is not recommended to use reserved words for anything else , if only to avoid confusion.

Narayan