i want to show columns in listbox9.but show only filter data.please help me .

mobin

New Member
Joined
Nov 28, 2018
Messages
20
Sheets("Transaction Account").Range("v14").AutoFilter Field:=22, Criteria1:=Me.TextBox69.Value

Dim Ary As Variant
Dim r As Long, c As Long, nr As Long

With Sheets("Transaction Account").ListObjects("Table6")
r = .ListColumns(1).Range.SpecialCells(xlVisible).Count
If r = 1 Then Exit Sub
ReDim Ary(1 To r - 1, 1 To .ListColumns.Count)
For r = 1 To .DataBodyRange.Rows.Count
If .DataBodyRange.Rows(r).Hidden = False Then
nr = nr + 1

Ary(nr, 1) = .DataBodyRange.Cells(r, 1)
Ary(nr, 2) = .DataBodyRange.Cells(r, 2)
Ary(nr, 3) = .DataBodyRange.Cells(r, 3)
Ary(nr, 4) = .DataBodyRange.Cells(r, 4)
Ary(nr, 5) = .DataBodyRange.Cells(r, 5)
Ary(nr, 6) = .DataBodyRange.Cells(r, 6)
Ary(nr, 7) = .DataBodyRange.Cells(r, 7)
Ary(nr, 8) = .DataBodyRange.Cells(r, 8)
Ary(nr, 9) = .DataBodyRange.Cells(r, 9)
Ary(nr, 10) = .DataBodyRange.Cells(r, 10)
Ary(nr, 11) = .DataBodyRange.Cells(r, 11)
Ary(nr, 12) = .DataBodyRange.Cells(r, 12)
Ary(nr, 13) = .DataBodyRange.Cells(r, 13)


Ary(nr, 14) = .DataBodyRange.Cells(r, 14)
Ary(nr, 15) = .DataBodyRange.Cells(r, 15)
Ary(nr, 16) = .DataBodyRange.Cells(r, 16)
Ary(nr, 17) = .DataBodyRange.Cells(r, 17)
Ary(nr, 18) = .DataBodyRange.Cells(r, 18)
Ary(nr, 19) = .DataBodyRange.Cells(r, 19)
Ary(nr, 20) = .DataBodyRange.Cells(r, 20)
Ary(nr, 21) = .DataBodyRange.Cells(r, 21)
Ary(nr, 22) = .DataBodyRange.Cells(r, 22)
Ary(nr, 23) = .DataBodyRange.Cells(r, 23)

End If
Next r
End With
Me.ListBox9.List = Ary
 

Attachments

  • Capture.PNG
    Capture.PNG
    9.2 KB · Views: 4

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
You should spend some of your time to explain your problem, how you tried to fix it, what didn't work.
 

mobin

New Member
Joined
Nov 28, 2018
Messages
20
i want filter data with this row. But when i filter show only filter data didn't show the column.
 

Attachments

  • Capture.PNG
    Capture.PNG
    34.5 KB · Views: 3

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
So you want that also the column headers are copied to the array and then to the listbox, correct?

In this case you should refer to the table "Range" rather than its "DataBodyRange" (that doesn't include the header row).
Also, your loop could be simplified with an inner loop, as follows:
VBA Code:
With Sheets("Transaction Account").ListObjects("Table6")
    r = .ListColumns(1).Range.SpecialCells(xlVisible).Count
    If r = 1 Then Exit Sub
    ReDim Ary(1 To r - 0, 1 To .ListColumns.Count)     
    For r = 1 To .Range.Rows.Count                     
        If .Range.Rows(r).Hidden = False Then           
            nr = nr + 1
            For j = 1 To .ListColumns.Count
                Ary(nr, j) = .Range.Cells(r, j)
            Next j
        End If
    Next r
End With
Me.ListBox9.RowSource = ""
Me.ListBox9.List = Ary
Bye
 

Watch MrExcel Video

Forum statistics

Threads
1,129,395
Messages
5,636,057
Members
416,895
Latest member
SteveRandall

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
Top