On Jul 1, 10:33*am, "Rick Rothstein \(MVP -
VB\)"
wrote:
Do not set the RowSource in the Properties box, set it in code instead. Try
using something like this in your UserForm_Initialize event...
Dim LastRow As Long
LastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
ListBox1.RowSource = "A1:H" & LastRow
Rick
"Shazi" wrote in message
...
Hi,
I have a ListBox1 which is connected to the "DailyPurchase" worksheet,
I set the RowSource : DailyPurchase!A5:H2000.
DailyPurchase sheet is updated daily basis at least 20 entries per
day.
and my DailyPurchase sheet have 200 records at present, and its
increasing day by day.
the problem is this when I load the userform it shows lot of blank
rows at bottom in the listbox, which I dont want to see.
I want to see only current entries/records in the ListBox. *if I add
20 or more than 20 records per day, the listbox shows the actual
records in the listbox.
If any one knows how to do this pls help *me out.
Best Regards.
Syed Shahzad Zafar
Madinah- Hide quoted text -
- Show quoted text -
Hi,
I am using the below code in my userform, to hide unwanted blank rows
in Listbox,
My data is coming after filtration procedure to AP5:AY
filtered data is veriable, some times 5 rows and some time 50 rows and
some time 70 rows. I want to see only the filtered rows in the list
box.
but this procedure is not working in my new form.
your procedure is working in other file, but in this file its making
problem.
Option Explicit
Dim LastRow As Long
Private Sub Userform_Initialize()
Sheets("DailyIssue").Select
'for Listbox Scrol bar hide unwanted rows.
LastRow = Worksheets("DailyIssue").Cells(Rows.Count,
"a").End(xlUp).Row
ListBox1.RowSource = "AP5:AY" & LastRow
StartDate = ""
EndDate = ""
MaterialName = ""
CoboCategory = ""
TxtTRNo = ""
CoboEmployee = ""
StartDate.SetFocus
End Sub
Private Sub CommandButton1_Click()
range("A4:J5000").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=range _
("AP1:AY3"), CopyToRange:=range("AP4:AY500"), Unique:=False
Selection.End(xlUp).Select
Sheets("DailyIssue").Select
range("AP3:AY3") = ""
End Sub
Can you check where is the mistake.
Best Regards
Syed Shahzad Zafar