Blank entry in drop down list

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
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,

Code:
'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,
Code:
=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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
With just the header R1 and ANYTHING in R2
Code:
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.....
Code:
lastrowr = Sheets("INFO").Cells(Rows.Count, "R").End(xlUp).Row -1

Hope that helps.
 
Upvote 0
That didnt quite work.

With the advice above the code now looks like this.

Code:
'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.

Code:
ComboBox1.List = Sheets("INFO").Cells(2, "R").Resize(lastrowr).Value
 
Upvote 0
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()
Code:
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
 
Upvote 0
Hi,
below is my in use Initialize code.

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
 
Upvote 0
Sorry, but I cannot appear to get rid of the extra blank row while using the .List method.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top