How to export filtered listbox contents into an excel file?

Waimea

Active Member
Joined
Jun 30, 2018
Messages
465
Office Version
  1. 365
Platform
  1. Windows
I am using an userform with a listbox and I want to export the filtered listbox contents into an excel file.

Code:
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:

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?
 
I got both textboxes and comboboxes working with your help!

Thank you again Fluff for your help! :)
 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Glad you sorted it & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top