PDA

View Full Version : Solved: Filter worksheet using List Box (multi select) Selections



Shortz
04-15-2010, 04:53 AM
Hi Everyone! Thanks for your help so far, its been great! Also thanks in advance for any assistance given.

I think i'm getting the question i want.

I wish to know how to: use the selected items from a multi select list box to filter a worksheet or set of data.

More specifically, the user chooses a number of criteria e.g. names from the list box, and i wish the macro to run along a row and where it finds names that aren't selected to delete the column. Thus compacting the row to selected names.

I'm aware of filters, sorting and auto filters, so for the purposes of this please assume they can't be used.

mdmackillop
04-15-2010, 09:43 AM
Option Explicit

Private Sub UserForm_Initialize()
ListBox1.List() = Application.Transpose(Range("MyData"))
End Sub

Private Sub CommandButton1_Click()
Dim x%, j%, i%
Dim arr()
x = ListBox1.ListCount - 1
ReDim arr(x)
For i = 0 To x
If ListBox1.Selected(i) = True Then
arr(j) = ListBox1.List(i)
j = j + 1
End If
Next i
ReDim Preserve arr(j - 1)
On Error Resume Next
For i = x + 1 To 1 Step -1
If Not Application.Match(Cells(1, i), arr, 0) > 0 Then
Cells(1, i).EntireColumn.Delete
End If
Next
End Sub

Shortz
04-16-2010, 12:09 AM
Option Explicit

Private Sub UserForm_Initialize()
ListBox1.List() = Application.Transpose(Range("MyData"))
End Sub

Private Sub CommandButton1_Click()
Dim x%, j%, i%
Dim arr()
x = ListBox1.ListCount - 1
ReDim arr(x)
For i = 0 To x
If ListBox1.Selected(i) = True Then
arr(j) = ListBox1.List(i)
j = j + 1
End If
Next i
ReDim Preserve arr(j - 1)
On Error Resume Next
For i = x + 1 To 1 Step -1
If Not Application.Match(Cells(1, i), arr, 0) > 0 Then
Cells(1, i).EntireColumn.Delete
End If
Next
End Sub


Awesome! Thanks for the code, i'm yet to test it, but i had a couple of questions if its cool just about the coding because i'm yet to use arrays.

1. x% j% does that automatically dim them as integer or longs?

2. Why do you Redim arr(x)? Is this to set the initial size of it?

3. Why do you Redim preserve arr(j-1)? Is it to make it more compact and efficient?

4. Why do you add the 'on error resume next' there? Is that in case there is an error with scrolling through the columns?

5. Can i name my array anything so long as its name() ?

thanks for the help and sorry for the newb questions. :beerchug:

mdmackillop
04-16-2010, 12:49 AM
1. x% j% does that automatically dim them as integer or longs?
Yes

2. Why do you Redim arr(x)? Is this to set the initial size of it?
Yes. If you set the size in the Dim statement, you can't resize it

3. Why do you Redim preserve arr(j-1)? Is it to make it more compact and efficient?
Yes, but probably not necessasry here in this contect

4. Why do you add the 'on error resume next' there? Is that in case there is an error with scrolling through the columns?
Match will error if no match found. This keeps the loop going

5. Can i name my array anything so long as its name() ?
Yes, but avoid "reserved" words and words like "String" used in code.