Create a Combo Box from a copied range

Question

I have selected a range which I want to display on Combo Box. The code is as followed.

Sheet5.Activate
ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:= _
    "=*E*", Operator:=xlAnd
Range("A1").Select
ActiveCell.Offset(1, 2).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Without changing the above code, is there a way to change the below code to select the Selection.Copy?

or is there a way to change the Range(Selection, Selection.End(xlDown)) portion to above Selection.Copy?

With cbE
    Me.cbE.List = Worksheets("EMP").Range(Selection, Selection.End(xlDown)).Value
End With

How do I filter out rows (based on the 1st column containing a specific text) from a table and display filtered column (4th filtered column) on combo box?

Update

    With Sheet4
        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        Dim filterRange As Range
        Set filterRange = .Range("A2:A" & lastRow)
        
        With filterRange
            .AutoFilter 1, "E"
            Dim filterE As Range
            Set filterE = .Range("A1:A" & lastRow).Columns(5).SpecialCells(xlCellTypeVisible)
        End With
    End With
    Me.cbE.List = Worksheets("EMP&Customers").Range(filterE, filterE.End(xlDown)).Value

above is the latest code I have and it works fine except that any cells between the first and last filtered cell will also be visible on the combobox.

Issue_elaboration

No you can only refer to the selected range. Once you copy it and select something else Excel cannot tell anymore which range is in the clipboard. • Actually your question seems to be a [X/Y problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem): you ask for X but your problem is Y. Please [edit] and include what you actually want to achieve. • Also make sure you don't use `.Activate` and `.Select` this is a very bad practice: See [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Jan 19 '21 at 07:47
Is it wrong to suppose that filtering before filling the combo with the range, **you need to fill the combo with the filtered range**? If yes, theoretically you should use `SpecialCells(xlCellTypeVisible)`, but if you try using `List` with such a discontinuous range, it will load only its first area. You should process the range to make a continuous one from its existing areas. Then, selecting for such a purpose, is only something consuming Excel resources without bringing any benefit... — FaneDuru, Jan 19 '21 at 08:11
okay so if I were to change my question. how do I filter out rows (1st column containing a specific text) from a table and display filtered column (4th filtered column) on combo box? — Hyun E, Jan 19 '21 at 08:19
@HyunE did you do some research? The internet is full of examples how to fill a combo box with filtered values. — Pᴇʜ, Jan 19 '21 at 08:21
I've been stuck on this issue for hours. If I can simply google and find the solution wouldnt put effort to ask here. So please do not talk to me like that. — Hyun E, Jan 19 '21 at 08:24
@as FaneDuru already said you need to use `SpecialCells(xlCellTypeVisible)` to get only the visible cells of your filtered range. See https://www.mrexcel.com/board/threads/use-filtered-collumn-to-populate-combobox.913326/ give that a try. — Pᴇʜ, Jan 19 '21 at 08:28

score 0 · Answer 1 · answered Jan 19 '21 at 07:53

Set your selected range to a variable that you can use later, to refer to that range even if the selection changed.

With Sheet5
    .ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:="=*E*", Operator:=xlAnd

    Dim CopiedRange As Range 'set your range to a variable
    Set CopiedRange = .Range(.Cells(2, 3), .Cells(2, 3).End(xlDown))

    CopiedRange.Copy 'you can remove this line unless you want to paste the copied range somewhere.
End With

'Use this variable to refer to the range
Me.cbE.List = Worksheets("EMP").Range(CopiedRange, CopiedRange.End(xlDown)).Value

FaneDuru · Answer 2 · 2021-01-19T09:39:21.220

0

Please, try the next approach:

Copy the next code in the form code module (If its Initialize event is already used, please, also add the following event code):

Private Sub UserForm_Initialize()
  Dim sh As Worksheet, rngVis As Range, arrVis, cbE As MSForms.ComboBox
  
  Set sh = Sheet5 'ActiveSheet - I used it to make some tests...
  With sh
     .ListObjects("Table6").Range.AutoFilter field:=1, Criteria1:="=*E*", Operator:=xlAnd
     Set rngVis = .Range(.cells(2, 3), .cells(2, 3).End(xlDown)).SpecialCells(xlCellTypeVisible)
  End With
  arrVis = makeContRng(rngVis)
  Me.cbE.list = WorksheetFunction.Transpose(arrVis)
End Sub

Private Function makeContRng(rng As Range) As Variant 'continuous with one column
    Dim arr, ar As Range, iRow As Range, i As Long, j As Long, k As Long
    
    ReDim arr(1 To rng.cells.count, 1 To 1)
    For Each ar In rng.Areas
        k = k + 1
         arr(k, 1) = ar.Value
    Next ar
    makeContRng = arr
End Function

edited Jan 19 '21 at 09:39

answered Jan 19 '21 at 08:57

FaneDuru

38,298
4
19
27

Thank you very much but Im getting a type mismatch error at ``` .list = WorksheetFunction.Transpose(arrVis) ```line – Hyun E Jan 19 '21 at 09:26
@Hyun E: Strange... Please, add this code line before `With Me.cbE`: `Debug.Print Ubound(arrVis), Ubound(arrVis, 2): Stop` and run the code again. The code will stop on the added line. What does it return in Immediate Window (being in VBE, press `Ctrl + G`)? Are you sure that you want processing the C:C column? – FaneDuru Jan 19 '21 at 09:31
@Hyun E: Did you find some time to test my suggestion? If something unclear, please do not hesitate to ask... – FaneDuru Jan 19 '21 at 10:38

Create a Combo Box from a copied range

2 Answers2