Limit number of results shown in List Box

[Guard913]

Current Code Works Perfectly!!! However, the idiots who designed the excel sheet (I have no control over this) Decided it would be funny to double, triple and even quadrupedal the results maybe even higher than that not sure.... There are 28000+ Lines/Rows.... However.. I only need the first 3 Results... LOL... How can I get the macro to stop after 3 Rows are filled.... So my list box looks much nicer....

Private Sub CommandButton9_Click()
On Error GoTo Terminate
 
 Dim rng As Range
 Dim Cel1 As Range
 Dim LR As Long
 Dim ws As Worksheet
 Set ws = Sheets("Rates")
 With ws
 LR = .Cells(.Rows.Count, "D").End(xlUp).Row
 Set rng = .Range("D1:D" & LR).SpecialCells(xlCellTypeVisible)
 With Me.ListBox1
 .ColumnCount = 5 '
 For Each Cel1 In rng
 .AddItem CStr(Cel1.Value)
 .List(.ListCount - 1, 1) = Cel1.Offset(0, 5).Value
 .List(.ListCount - 1, 2) = Cel1.Offset(0, 6).Value
 .List(.ListCount - 1, 3) = Cel1.Offset(0, 7).Value
 .List(.ListCount - 1, 4) = Cel1.Offset(0, 8).Value
 .List(.ListCount - 1, 5) = Cel1.Offset(0, 9).Value
 .List(.ListCount - 1, 6) = Cel1.Offset(0, 10).Value


 Next Cel1
 End With
 End With
 
Exit Sub
Terminate:
ListBox1.Clear
Error.Show

End Sub

Thanks!!!

 

[Coding4Fun]

You should be able to simply adjust this line, adjusting LR to D3 to indicate your range is D1 to D3 (The first 3 rows) and drop the LR altogether

LR = .Cells(.Rows.Count, "D").End(xlUp).Row
Set rng = .Range("D1:D" & LR).SpecialCells(xlCellTypeVisible)

Set rng = .Range("D1:D3").SpecialCells(xlCellTypeVisible)

 

[Guard913]

You should be able to simply adjust this line, adjusting LR to D3 to indicate your range is D1 to D3 (The first 3 rows) and drop the LR altogether

LR = .Cells(.Rows.Count, "D").End(xlUp).Row
Set rng = .Range("D1:D" & LR).SpecialCells(xlCellTypeVisible)

Set rng = .Range("D1:D3").SpecialCells(xlCellTypeVisible)

No go..... If i remove LR from the code entirely it will now only put the first line. And Yes I tried D1:D3... Thanks for your attempt!!

 

[Coding4Fun]

Tested this one should work. I get the first 3 rows as items added to the list. I added an additional line to clear the list at the start of the click otherwise you will put the first 3 line into the list over and over each time the button is clicked.

Test1
Test2
Test3
Test1
Test2
Test3 etc.

Private Sub CommandButton1_Click()

Me.ListBox1.Clear

On Error GoTo Terminate
 
 Dim rng As Range
 Dim Cel1 As Range
 Dim LR As Long
 Dim ws As Worksheet

 Set ws = Sheets("Rates")
 With ws
 Set rng = .Range("D1:D3")
 With Me.ListBox1
 .ColumnCount = 5 '
 For Each Cel1 In rng
 .AddItem CStr(Cel1.Value)
 Next Cel1
 End With
 End With
 
Exit Sub
Terminate:
Me.ListBox1.Clear
Error.Show


End Sub

 

[Guard913]

Ok... I should I explain a bit more... Prior to all this Filters run from form changing excel sheet... While your new code certainly does just bring the first 3 Items it starts at the top of the list vs just on the filtered part. (As you can see we are at the 11k-14k Line mark here. lol) Also I don't want it to clear everytime I push search because sometimes I need to change term, to see the different prices for diff. lengths to compare. So having it add on top is perfect. ie why form has a clear list button.

Now with your code, and mixed with mine:

(I have to have this)

 .List(.ListCount - 1, 1) = Cel1.Offset(0, 5).Value
 .List(.ListCount - 1, 2) = Cel1.Offset(0, 6).Value
 .List(.ListCount - 1, 3) = Cel1.Offset(0, 7).Value
 .List(.ListCount - 1, 4) = Cel1.Offset(0, 8).Value
 .List(.ListCount - 1, 5) = Cel1.Offset(0, 9).Value
 .List(.ListCount - 1, 6) = Cel1.Offset(0, 10).Value

Private Sub CommandButton9_Click()
On Error GoTo Terminate
 
 Dim rng As Range
 Dim Cel1 As Range
 Dim LR As Long
 Dim ws As Worksheet

 Set ws = Sheets("Rates")
 With ws
 Set rng = .Range("D1:D4")
 With Me.ListBox1
 .ColumnCount = 5 '
 For Each Cel1 In rng
 .AddItem CStr(Cel1.Value)
 .List(.ListCount - 1, 1) = Cel1.Offset(0, 5).Value
 .List(.ListCount - 1, 2) = Cel1.Offset(0, 6).Value
 .List(.ListCount - 1, 3) = Cel1.Offset(0, 7).Value
 .List(.ListCount - 1, 4) = Cel1.Offset(0, 8).Value
 .List(.ListCount - 1, 5) = Cel1.Offset(0, 9).Value
 .List(.ListCount - 1, 6) = Cel1.Offset(0, 10).Value
 
 Next Cel1
 End With
 End With
 
Exit Sub
Terminate:
Me.ListBox1.Clear
Error.Show
End Sub

 

[Guard913]

Forgot to add... Your code as is without mixing with mine pulls this:

 

[Coding4Fun]

Private Sub CommandButton9_Click()
On Error GoTo Terminate
 
 Dim rng As Range
 Dim Cel1 As Range
 Dim LR As Long
 Dim ws As Worksheet
 Set ws = Sheets("Rates")
 With ws
 LR = .Cells(.Rows.Count, "D").End(xlUp).Row
 Set rng = .Range("D1:D" & LR).SpecialCells(xlCellTypeVisible)
 onrow = 1
 With Me.ListBox1
 .ColumnCount = 5

 For Each Cel1 In rng
 If onrow <= 4 And onrow > 1 Then ' Only add the value if working on the first 3 results in our range excluding the header
 .AddItem CStr(Cel1.Value)
 .List(.ListCount - 1, 1) = Cel1.Offset(0, 5).Value
 .List(.ListCount - 1, 2) = Cel1.Offset(0, 6).Value
 .List(.ListCount - 1, 3) = Cel1.Offset(0, 7).Value
 .List(.ListCount - 1, 4) = Cel1.Offset(0, 8).Value
 .List(.ListCount - 1, 5) = Cel1.Offset(0, 9).Value
 .List(.ListCount - 1, 6) = Cel1.Offset(0, 10).Value
 onrow = onrow + 1
 Else
 If onrow > 4 Then Exit Sub
 onrow = onrow + 1
 End If

 Next Cel1
 End With
 End With
 
Exit Sub
Terminate:
ListBox1.Clear
Error.Show

End Sub

 

[Guard913]

That is like so close, I can probably fiddle with it, it shows the 3 items perfectly... it just doesn't add the header...

I am so grateful you took the time to get this much done!! So grateful!!!!

 

[Guard913]

That is like so close, I can probably fiddle with it, it shows the 3 items perfectly... it just doesn't add the header...

I am so grateful you took the time to get this much done!! So grateful!!!!

Fixed It!!!

If onrow <= 4 And onrow > 0 Then ' Only add the value if working on the first 3 results in our range excluding the header

 

[Coding4Fun]

Glad you were able to adjust to fit your need, happy to help