Filtering on a multiselect listbox

[wabtrainer]

I have a userform that has a textbox to enter a search string into and two buttons.
The string is searching from a range of two columns and the first button displays the entire contents of the cells that contain the entered string.
I now want to have the entire sheet shown but filtered for the rows that were selected in the listbox.
Either the sheet filtered or copied to a new sheet.
I can not think how to do this.
Could somebody kindly point me in the right direction?

Thanks


If you want to be a bear:
Be a Grizzly!

 

[SkipVought]

Hi,

What code do you have so far?

Have you tried using the AutoFilter and your macro recorder?

Skip,
[sub]
[red][/red]
[/sub]

 

[wabtrainer]

This is the code that is on the button to populate the list box.

Private Sub CommandButton1_Click()
Dim txtStr As String
txtStr = "*" & txtFind.Value & "*"

With Worksheets("Assumptions").Range("txtSearch")
Application.Cursor = xlWait
For Each c In Worksheets("Assumptions").Range("txtSearch").Cells


If Not c = "" Then

If c Like txtStr Then
Me.listFind.AddItem (c.Value)
'Debug.Print c.Value
End If

End If

'Set c = .FindNext(c)

Next
End With
Application.Cursor = xlDefault
End Sub

It works fine but once the items are selected I want to view the whole of the sheet (16 columns, 800 rows) where the text entered appears, but only thos rows.


If you want to be a bear:
Be a Grizzly!

 

[wabtrainer]

I thought that something like:

For i = 0 To (Length of list)
If ListFind.Selected(i) = True Then
(Copy the row that it comes from to a new sheet?)
End If
Next i

might work, but dont know how to do the bits in brackets!

i am learning all this on the fly!

Cheers


If you want to be a bear:
Be a Grizzly!

 

[SkipVought]

Have you checked out the AutoFilter?

Then you can select the visible data, copy 'n' paste special - values to another sheet in just a few statements.

Skip,
[sub]
[red][/red]
[/sub]

 

[wabtrainer]

OK, but how do I apply an auto filter to only those items selected in a listbox?


If you want to be a bear:
Be a Grizzly!

 

[SkipVought]

Have you macro recorded setting multiple criteria in the AutoFilter?

Skip,
[sub]
[red][/red]
[/sub]

 

[wabtrainer]

Cracked it!

A bit lengthy way to get round it in the end but it seems to work ok.
It allows the user to enter a string which could occur any where in either column C or column D.
A multiselect listbox displays the values for each cell containing the string.
The user selects those he wants to view and the rows are copied to a temporary sheet.
Effectively allowing a multi column filter and the press of a button.
I would post a sample if you could.

Cheers for the help though SkipVought, new ideas can sometimes come out of the smallest hint or tip.


If you want to be a bear:
Be a Grizzly!