How to export filtered listbox contents into an excel file?

[Waimea]

I am using an userform with a listbox and I want to export the filtered listbox contents into an excel file.

myarray = Sheet61.ListObjects("Data").DataBodyRange.Value


With ListBox1
.List = myarray
.ColumnCount = 25
.ColumnHeads = False
End With

This code loads the DataBodyRange.Value into my listbox. I then filter it using the following code:

Private Sub TextBox1_Change()Application.ScreenUpdating = False
    
    Dim endarr(), ListEndRow As Long, lrows As Long, i As Long, j As Long, k As Long
    If TextBox1.Text = vbNullString Then ListBox1.List = myarray: Exit Sub
                
    ListEndRow = 1
    With Sheet61
        lrows = .ListObjects("Data").DataBodyRange.Rows.Count
        ReDim endarr(1 To lrows, 1 To 21)
        For i = 1 To UBound(myarray)
            If Left(LCase(myarray(i, 5)), LCase(Len(TextBox1.Text))) = LCase(TextBox1.Text) Then
                For j = 1 To 20
                    endarr(ListEndRow, j) = myarray(i, j)
                Next
                ListEndRow = ListEndRow + 1
            End If
        Next
    End With
    ListBox1.List = endarr
    
    Me.TextBox1.BackColor = RGB(231, 125, 0)
    Application.ScreenUpdating = True
End Sub

1. Where should I start to export the filtered listbox to an excel file?

2. How can I edit things in the array? Or should I edit the databodyrange and then reload the array?

 

[Fluff]

How about

   End With
   ListBox1.List = Endarr
   [COLOR=#0000ff]Sheets("Sheet1").Range("A1").Resize(UBound(Endarr), UBound(Endarr, 2)).Value = Endarr[/COLOR]
   Me.TextBox1.BackColor = RGB(231, 125, 0)

 

[Waimea]

Hi Fluff,

thank you for your reply. Your code works and it updates the worksheet whenever I change the filtering in my userform.

I would ideally like a command button that exports Endarr to an Excel file?

I have tried declaring Endarr as a global variable?

 

[Waimea]

How can I access endarr outside of the private sub?

 

[Fluff]

No need to declare it as global, you can do it like

Private Sub CommandButton1_Click()
   Dim Ary As Variant
   
   Ary = Me.ListBox1.List
   Sheets("Sheet1").Range("A1").Resize(UBound(Ary), UBound(Ary, 2)).Value = Ary
End Sub

 

[Waimea]

Hi Fluff,

thank you for your reply! Your code does exactly what I am trying to do!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Waimea]

I am using 4 instances of this code to filter on different things.

Private Sub TextBox1_Change()

Application.ScreenUpdating = False
    
    Dim endarr(), ListEndRow As Long, lrows As Long, i As Long, j As Long, k As Long
    If TextBox1.Text = vbNullString Then ListBox1.List = myarray: Exit Sub
                
    ListEndRow = 1
    With Sheet61
        lrows = .ListObjects("Data").DataBodyRange.Rows.Count
        ReDim endarr(1 To lrows, 1 To 21)
        For i = 1 To UBound(myarray)
            If Left(LCase(myarray(i, 5)), LCase(Len(TextBox1.Text))) = LCase(TextBox1.Text) Then
                For j = 1 To 20
                    endarr(ListEndRow, j) = myarray(i, j)
                Next
                ListEndRow = ListEndRow + 1
            End If
        Next
    End With
    ListBox1.List = endarr
    
    Me.TextBox1.BackColor = RGB(231, 125, 0)
    Application.ScreenUpdating = True
End Sub

Is there an easy way to filter with multiple criteria with this code? I am changing

 If Left(LCase(myarray(i, 5)), LCase(Len(TextBox1.Text))) = LCase(TextBox1.Text) Then

this part in the code to filter on one thing. Can it be combined?




Say TextBox i is Country, TextBox2 is City, TextBox3 is Name, TextBox4 is Muncipality.

So if I filter on Sweden is get all items where this is true.

I then continue to filter on City and I get all items in Sweden and City.

Additionally, I filter on Name and I get all items where Sweden, City and Name are True.

Finally, I filter on Muncipality and I select Stockholm and get all items where these 4 criteria are true.

 

[Fluff]

Maybe something like

If LCase(myarray(i, 5)) like, LCase(TextBox1.Text)&"*" myarray(i,1)=combobox1 & myarray(i,3)=combobox2. then

 

[Waimea]

Hi again Fluff,

thank you for your reply. You are very kind!

Your code doesn't compile for me? I am not sure why?