How to show just filtered rows in my userform listbox

nimavao

New Member
Joined
Dec 3, 2016
Messages
3
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>
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
I assume that your command button is on the UserForm. If Not, this code fails.

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

nimavao

New Member
Joined
Dec 3, 2016
Messages
3
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


 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

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