How can i amend this code to go in listbox

[Eisasuarez]

Hi

How can i amend this so the results go into a list box rather than the sheet cells?

I have 4 columns

Private Sub CmdFilter_Click()
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
ws.Range("A5:D17").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("F5:F6"), _
CopyToRange:=Range("H5:K5"), _
Unique:=False
End Sub


Thanks

 

[Eisasuarez]

Hi

How can i amend this so the results go into a list box rather than the sheet cells?

I have 4 columns

Private Sub CmdFilter_Click()
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
ws.Range("A5:D17").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("F5:F6"), _
CopyToRange:=Range("H5:K5"), _
Unique:=False
End Sub


Thanks

Any luck from one of you lovely people

 

[Andrew Poulsom]

Try:

Private Sub CmdFilter_Click()
    Dim ws As Worksheet
    Dim LR As Long
    Set ws = Sheets("Sheet1")
    With ws
        .Range("A5:D17").AdvancedFilter _
            Action:=xlFilterCopy, _
            CriteriaRange:=.Range("F5:F6"), _
            CopyToRange:=.Range("H5:K5"), _
            Unique:=False
        LR = .Range("H5").End(xlDown).Row
        ListBox1.List = .Range("H5:K" & LR).Value
    End With
End Sub

 

[Eisasuarez]

Thank you Andrew

can you please advise if this is correct

1) if i am transferring data from sheet to listbox or transferring multiple columns of data, use this method
listbox.list=

when you use add item?

 

[Andrew Poulsom]

You can use AddItem, but then you would have to populate the remaining columns. Using List is a lot easier.

 

[Eisasuarez]

Thats cool
i wernt sure when to use one over the other

i guess the way to go with list box is use list and not additem

 

[Eisasuarez]

Hi Andrew

just another quick question

say i was looping in a range (a1:a10) and i was using an if statement to see if the cell value = Apple (say A4 contained Apple, then copy (A4:C4) to the list box

Can i use list?

i know how to use an if statement and loop

for each cell in range("a1:a10")
If cell.value = "apple" then
listbox.list = ????
end if
next cell

 

[Eisasuarez]

Try:

Private Sub CmdFilter_Click()
    Dim ws As Worksheet
    Dim LR As Long
    Set ws = Sheets("Sheet1")
    With ws
        .Range("A5:D17").AdvancedFilter _
            Action:=xlFilterCopy, _
            CriteriaRange:=.Range("F5:F6"), _
            CopyToRange:=.Range("H5:K5"), _
            Unique:=False
        LR = .Range("H5").End(xlDown).Row
        ListBox1.List = .Range("H5:K" & LR).Value
    End With
End Sub

Hi Andrew

There is only one problem with this

H5:K5 are my headers and i have set the property of the listbox column header to true and column count to 3 however it is not showing the first row (H5:K5) as my column Header

 

[Andrew Poulsom]

To show a header you need to set the RowSource property:

ListBox1.RowSource = "'" & .Name & "'!" & .Range("H6:K" & LR).Address(0, 0)