How to dynamically add values starting at a specific cell until the last row and add the contents to a combobox

Pookiemeister

Active Member
Joined
Nov 26, 2015
Messages
306
Office Version
  1. 365
  2. 2010
The code below works, however, I would like the code to find and add all the items dynamically.

Code:
Private Sub ComboBox1_Enter()   
    If cmbSDPFLine.Value = "Line 1" Then
        With Me.ComboBox1
            .Clear
            .List = WorksheetFunction.Transpose(Sheets("Sheet1").Range("A3:A10").Value)
        End With
    End If
End Sub

I tried to make the code below dynamic but I get a "compile error: Method or data member not found".
Code:
Private Sub ComboBox1_Enter()
    If cmbSDPFLine.Value = "Line 1" Then
        With Me.ComboBox1
            .Clear
            .List = .Range("A3", .Range("A" & Rows.Count).End(xlUp).Offset)
        End With
    End If


End Sub
When I select debug the highlighted code is:
Code:
.Range
Followed by:
Code:
("A" & Rows.Count).End(xlUp))

Thank You
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,326
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
To start with you haven't included a value in the Offset in the line below
Code:
.List = .Range("A3", .Range("A" & Rows.Count).End(xlUp).Offset)
what error occurs when you use
Code:
.List = .Range("A3", .Range("A" & Rows.Count).End(xlUp).Offset(1))
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,326
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You also haven't referenced the sheet so test

Code:
.List = Sheets("Sheet1").Range("A3", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1))
 

Pookiemeister

Active Member
Joined
Nov 26, 2015
Messages
306
Office Version
  1. 365
  2. 2010
Thank you for your help. When I put replace your code with mine. I get a "Run time error '381': Could not set the list property. Invalid property array index.":confused:
Code:
Private Sub ComboBox1_Enter()
If cmbSDPFLine.Value = "SDPF - Line 1" Then
        With Me.ComboBox1
            .Clear
            .List = Sheets("Sheet1").Range("A3", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1))
        End With
End Sub
When I click debug, the below code is highlighted in yellow.
Code:
[COLOR=#333333].List = Sheets("Sheet1").Range("A3", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1))[/COLOR]
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
Try
Code:
            .List = Sheets("Sheet1").Range("A3", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)).Value
 

Watch MrExcel Video

Forum statistics

Threads
1,109,041
Messages
5,526,417
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top