Blank entry in drop down list

[ipbr21054]

Morning,
I have a userform which has many comboboxes with drop down lists.
I have noticed that if a drop down list entry only shows N/A then below it is a blank entry also.
Anything else where there are 2,3 or 20 options to select then the blank space at the end isnt shown.

The sheet where my details are stored is called INFO
Example of its table
R1 is the header called REGISTRATION
R2 is the entry N/A
There is then nothing else shown below it.

On the userform if i right click the combobox & select view code there is nothing shown BUT if i click a blank space on the form i then see its code so for this case i see the following,

'REGISTRATION NUMBERDim lastrowr As Long
lastrowr = Sheets("INFO").Cells(Rows.Count, "R").End(xlUp).Row
ComboBox1.List = Sheets("INFO").Cells(2, "R").Resize(lastrowr).Value

Looking in name manager i see,

=Table10[REGISTRATION]

Then,
Table10 & in the refers to field =INFO!$R$2:$R$2

I cant see why or understand why that any entry that i only want N/A applied has the blank space below it.

Thanks

 

[Snakehips]

With just the header R1 and ANYTHING in R2

lastrowr = Sheets("INFO").Cells(Rows.Count, "R").End(xlUp).Row

will return 2
Then resizing R2 by 2 will include R2 (N/R) AND R3 which is blank. Same would apply if your list extended down beyond R2........ your code is generating the blank.

Try.....

lastrowr = Sheets("INFO").Cells(Rows.Count, "R").End(xlUp).Row -1

Hope that helps.

 

[ipbr21054]

That didnt quite work.

With the advice above the code now looks like this.

'REGISTRATION NUMBERDim lastrowr As Long
lastrowr = Sheets("INFO").Cells(Rows.Count, "R").End(xlUp).Row - 1
ComboBox1.List = Sheets("INFO").Cells(2, "R").Resize(lastrowr).Value

I open the worksheet & press the button to show me the userform but i see a messqage,
Run Time Error 381
Could not set the list property Invalid property array index.

The userform didnt open.

I click debug and this part is shown in yellow.

ComboBox1.List = Sheets("INFO").Cells(2, "R").Resize(lastrowr).Value

 

[Snakehips]

I'm not exactly sure what you have or where when you are using that code but if it is a Userform Combo then I imagine that I would be using like below in say the userform Initialize...Private Sub UserForm_Initialize()

Dim lastrowr As Long
lastrowr = Sheets("INFO").Cells(Rows.Count, "R").End(xlUp).Row - 1
Me.ComboBox1.RowSource = Sheets("INFO").Cells(2, "R").Resize(lastrowr).Address
End Sub

 

[ipbr21054]

Hi,
below is my in use Initialize code.

Private Sub UserForm_Initialize()TextBox2.Value = Format(Date, "dd/mm/yyyy")


'REGISTRATION NUMBER
Dim lastrowr As Long
lastrowr = Sheets("INFO").Cells(Rows.Count, "R").End(xlUp).Row - 1
ComboBox1.List = Sheets("INFO").Cells(2, "R").Resize(lastrowr).Value


'BLANK USED
Dim lastrowl As Long
lastrowl = Sheets("INFO").Cells(Rows.Count, "L").End(xlUp).Row
ComboBox2.List = Sheets("INFO").Cells(2, "L").Resize(lastrowl - 1).Value


'VEHICLE
Dim lastrowb As Long
lastrowb = Sheets("INFO").Cells(Rows.Count, "B").End(xlUp).Row
ComboBox3.List = Sheets("INFO").Cells(2, "B").Resize(lastrowb - 1).Value


'BUTTONS
Dim lastrowj As Long
lastrowj = Sheets("INFO").Cells(Rows.Count, "J").End(xlUp).Row
ComboBox4.List = Sheets("INFO").Cells(2, "J").Resize(lastrowj - 1).Value


'ITEM SUPPLIED
Dim lastrowt As Long
lastrowt = Sheets("INFO").Cells(Rows.Count, "T").End(xlUp).Row
ComboBox5.List = Sheets("INFO").Cells(2, "T").Resize(lastrowt - 1).Value


'TRANSPONDER CHIP
Dim lastrowf As Long
lastrowf = Sheets("INFO").Cells(Rows.Count, "F").End(xlUp).Row
ComboBox6.List = Sheets("INFO").Cells(2, "F").Resize(lastrowf - 1).Value


'JOB ACTION
Dim lastrowh As Long
lastrowh = Sheets("INFO").Cells(Rows.Count, "H").End(xlUp).Row
ComboBox7.List = Sheets("INFO").Cells(2, "H").Resize(lastrowh - 1).Value


'PROGRAMMER USED
Dim lastrowd As Long
lastrowd = Sheets("INFO").Cells(Rows.Count, "D").End(xlUp).Row
ComboBox8.List = Sheets("INFO").Cells(2, "D").Resize(lastrowd - 1).Value


'KEY CODE
Dim lastrowp As Long
lastrowp = Sheets("INFO").Cells(Rows.Count, "P").End(xlUp).Row
ComboBox9.List = Sheets("INFO").Cells(2, "P").Resize(lastrowp).Value


'BITING
Dim lastrowx As Long
lastrowx = Sheets("INFO").Cells(Rows.Count, "X").End(xlUp).Row
ComboBox10.List = Sheets("INFO").Cells(2, "X").Resize(lastrowx - 1).Value


'CHASSIS NUMBER
Dim lastrown As Long
lastrown = Sheets("INFO").Cells(Rows.Count, "N").End(xlUp).Row
ComboBox11.List = Sheets("INFO").Cells(2, "N").Resize(lastrown).Value


'VEHCILE YEAR
Dim lastrowv As Long
lastrowv = Sheets("INFO").Cells(Rows.Count, "V").End(xlUp).Row
ComboBox12.List = Sheets("INFO").Cells(2, "V").Resize(lastrowv - 1).Value


'INVOICE NUMBER
Dim lastroww As Long
lastroww = Sheets("INFO").Cells(Rows.Count, "W").End(xlUp).Row
ComboBox13.List = Sheets("INFO").Cells(2, "W").Resize(lastroww).Value


End Sub

 

[Snakehips]

Sorry, but I cannot appear to get rid of the extra blank row while using the .List method.