Reading columns and rows from a sheet and bind to combo box VBA

Question

Im a very beginner with VBA. Ive got a scenario to decide the perfect way for binding and manipulate the data from different sheets in a custom form designed in another sheet on the same workbook.

There are 2 sheets one with Fruit names in different year and another with Quantity of every year.

Here is Sheet 1 data.

Based on this I need to bind these years to one dropdown, fruits to another dropdown (based on year it should load dynamically). Since my previous experience in .NET so if we consider all as a Datatable its easy to manipulate anything using some filters or just some loops or with LINQ. But in VBA how can we handle these bindings and manipulation based on these data. Also if the rows could not be always in specific cell ranges, there could be more. So how can we handle when dynamic data.

What I tried for binding is as follows based on this link

Dim vArr as Variant
Dim i as Integer
vArr = WorksheetFunction.Transpose(Sheets(2).Range("A2:A10").value)
With Sheets(1).OLEObjects("ComboBox1").Object
     .Clear
     For i = Lbound(vArr) to Ubound(vArr)
        .AddItem vArr(i)
     Next i
End With

That's not adding your years to your combobox? Looks like it should work to me. — K.Dᴀᴠɪs, Feb 04 '18 at 11:37
Sorry.. Its fine.. there is no issues.. But my issue was how can we accomplish the same if there is no specic end cell.. Like dynamic data. Also if I need to load the second dropdown based on the year selected in the first dropdown, is there is any easy solution like Selecting all rows where year is that selected year in dropdown 1 — Sandeep Thomas, Feb 04 '18 at 11:53

score 0 · Answer 1 · answered Feb 04 '18 at 11:49

You really shouldn't be asking two questions in the same question... But because you did, I can at least answer your second question of:

"...if the rows could not be always in specific cell ranges, there could be more. So how can we handle when dynamic data"

You can use this function to grab the last row:

Public Function lastRow(ByVal ws As Worksheet, Optional ByVal col As Variant = 1) As Long
    lastRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row
End Function

Which you could use like:

Range("A2:A" & lastRow(Worksheets(2)))

Now, when you make your first question make a little more sense I will update my answer.

So its for dynamic data. Isnt it.. I will try this and will let you know. Thanks.. — Sandeep Thomas, Feb 04 '18 at 11:54

Reading columns and rows from a sheet and bind to combo box VBA

1 Answers1