How to show just filtered rows in my userform listbox

[nimavao]

hi all,i am so sorry if i ask repetitious question but i can not find any related by my problem,however
I have one Excel sheet, one userform and a listbox is in userform. Now when i filter my sheet and update listbox by click on button that is on my user form i see all rows in list box. I mean listbox1 show all cells (filter+no filter).
I have a little in formation in VBA but i need the codes and it is so important for me. Please help me with detail and with file if possible.
My code so far for updating the list box:

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">Private Sub CommandButton1_Click()
CommandButton10.Visible = True
insertlist1.Visible = True
ListBox1.Visible = True
ListBox1.RowSource = "'NEWPRJ'!D7:D46"
End Sub</code>

 

[JLGWhiz]

I assume that your command button is on the UserForm. If Not, this code fails.

Private Sub CommandButton1_Click()
Dim v() As Variant
    cnt = 0
    For Each c In Sheets("NEWPRJ").Range("D7:D46")
        If c.EntireRow.Hidden = False Then
            ReDim Preserve v(0 To cnt)
                v(cnt) = c
                cnt = cnt + 1
        End If
    Next
Me.ListBox1.List = v
End Sub

 

[Smitty]

Welcome to the Board!

Crossposted: http://stackoverflow.com/questions/4...erform-listbox

It's not a big deal, but we ask that you point it out so multiple people in different places aren't trying to do the same thing for you.

Please see: Excelguru Help Site - A message to forum cross posters

 

[nimavao]

huge thank JLGWhiz
it is solve for me but now i have another problem that i think it is not easy to solve(maybe not,but i think it is so hard ) ,i have a 2sheet(shhet9 and sheet10) and a user form,i put command button on userform for printing the special area of sheet10,data should copy from sheet 9 to sheet 10 by step 3row by 3row then print the area of sheet10 and copy next 3rows and continue this proces until last row in sheet9.
i find way for do it but it is just work for some steps(i mean that i should right code for it each time),if you can pleas help me
and this is my code :


Sheet9.Activate
If Sheet9.Range("A2").Value = "" Then
MsgBox "you did not press the insert data button"
Sheet9.Activate
Range("A:A").Select
Selection.ClearContents
Exit Sub
Else
Sheet10.Range("I1").Value = Sheet9.Range("A2")
Sheet10.Range("I2").Value = Sheet9.Range("A3")
Sheet10.Range("I3").Value = Sheet9.Range("A4")
End If
Sheet10.Activate
Range("A4:M60").Select
With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With


ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,1,,,TRUE,,FALSE)"
Application.Wait (Now + TimeValue("0:00:1"))
If Sheet9.Range("A5").Value = "" Then
MsgBox "printing done"
Exit Sub
Else
Sheet10.Range("I1").Value = Sheet9.Range("A5")
Sheet10.Range("I2").Value = Sheet9.Range("A6")
Sheet10.Range("I3").Value = Sheet9.Range("A7")
End If
Sheet10.Activate
Range("A4:M60").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,1,,,TRUE,,FALSE)"
Application.Wait (Now + TimeValue("0:00:1"))
If Sheet9.Range("A8").Value = "" Then
MsgBox "printing done"
Exit Sub
Else
Sheet10.Range("I1").Value = Sheet9.Range("A8")
Sheet10.Range("I2").Value = Sheet9.Range("A9")
Sheet10.Range("I3").Value = Sheet9.Range("A10")
End If
Sheet10.Activate
Range("A4:M60").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,1,,,TRUE,,FALSE)"
Application.Wait (Now + TimeValue("0:00:1"))
If Sheet9.Range("A11").Value = "" Then
MsgBox "printing done"
Exit Sub
Else
.
.
.
.
.
.and it countinue