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

mobin

New Member
Joined
Nov 28, 2018
Messages
22
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: 5

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
You should spend some of your time to explain your problem, how you tried to fix it, what didn't work.
 
Upvote 0
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: 4
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

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