VBA to define a row as a named column

Posted on 2014-08-26
Last Modified: 2014-08-27
I am struggling to write the code to define the data in a given row, in this case row 4, as a named range.  Once I have the named range, I can use it to populate a combobox on a form using: UserForm1.ComboBox1.RowSource = "TheRange"

I've researched trying to transpose it.  I definitely want to avoid copying and pasting the data into a worksheet.

Any help greatly appreciated!

Question by:Ben Cheetham
    LVL 26

    Expert Comment

    No need to define a named range. Try this:
    Private Sub UserForm_Initialize()
        Me.ComboBox1.List = WorksheetFunction.Transpose(Range("A4:Z4))
    End Sub

    Open in new window

    Assuming the data is in column A to Z.

    Author Comment

    by:Ben Cheetham
    Love the simplicity of the code - thank you.

    However, the data goes beyond column z and this will vary.  But that's unimportant as I could set it to a column that it will never exceed.

    I am struggling, though, with referencing the row as it is not on the active sheet. You sound like you will know how to help!

    Thanks again.
    LVL 26

    Accepted Solution

    Just precede the Range with the sheet index or name. i.e.
    Me.ComboBox1.List = WorksheetFunction.Transpose(Sheets(1).Range("A4:Z4"))
    Me.ComboBox1.List = WorksheetFunction.Transpose(Sheets("FirstSheetName").Range("A4:Z4"))

    Open in new window

    To dynamically accommodate for changing last last column you can use the following:
    Me.ComboBox1.List = WorksheetFunction.Transpose(Sheets(1).Range("A4:" & Replace(Cells(4, Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column).Address(False, False), "1", "")))
    Me.ComboBox1.List = WorksheetFunction.Transpose(Sheets("FirstSheetName").Range("A4:" & Replace(Cells(4, Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column).Address(False, False), "1", "")))

    Open in new window


    Author Comment

    by:Ben Cheetham
    Hi again and thank you for persevering.  I had tried prefixing the range with the sheet name but hadn't been successful.

    Unfortunately, it is still not working.  (I haven't yet tried the dynamic version but this relies on the same principle).

    This is my code, in the code for the Userform (named "UserForm1").  I tried a bigger range but then reduced it to only three cells in case some of the formatting of later cells was causing an issue.

    When the form opens, the combobox doesn't list any values.

    Private Sub UserForm1_initialize()
        Me.ComboBox1.List = WorksheetFunction.Transpose(Sheets("Class").Range("A4:C4"))
    End Sub

    I really hope you can persevere again.

    Author Comment

    by:Ben Cheetham
    Hi Again

    I've worked out that it wasn't calling the Initialize macro and corrected this and your code works beautifully.

    Just fine tuning it so may have a further query.

    But thank you for your excellent help so far.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now