I have a Spreadsheet that contains a table, i am using a Userform to Filter the Table Data to show for example Parts in a Bill of Material that are sheet Metal or Purchased items.
as this Filtering Process changes the Part number Column, i would like the combobox on the userform to reflect the values in the now showing filter column of results.
At the moment The userform get the rowsource when the spreadsheet is unfiltered in the Userform initilisation sub
does anyone know of a solution that would help support my problem?
I have tried running a macro to select the source using
Dim RNG As Range
Set RNG = Nothing
Set RNG = Selection.SpecialCells(xlCellTypeVisible)
me.combobox2.list = RNG
and then making a reference to the selection, but i think there will be a better way.
You can loop through each cell within the visible range, and add those values to an array. Then assign the array to your listbox.
By the way, you'll get an error if an object is selected. I would suggest using the RangeSelection property of the ActiveWindow object instead.
Try something like this...
Dim selectedRange As Range
Set selectedRange = ActiveWindow.RangeSelection.SpecialCells(xlCellTypeVisible)
Dim filteredData() As Variant
ReDim filteredData(1 To selectedRange.Cells.Count)
Dim recordCount As Long
recordCount = 0
Dim currentCell As Range
For Each currentCell In selectedRange
recordCount = recordCount + 1
filteredData(recordCount) = currentCell.Value
Next currentCell
Me.ComboBox2.List = filteredData