Excel Listbox 1 row result >50 time listed

See more:

MS-Excel

VBA

I am currently autofiltering a range of data, I copy my filtered data to a new named range.

When I run the code below; in my userform Listbox, I get a row from the source range outside of my identified range below "rlist" with duplicated results inside the listbox around 50 time? thank you in advance for your help.


Set rlist = .Range(.Cells(2, 19), .Cells(2,35).EnDxlDown))

With Me.ListBox1
.Clear
.ColumnCount = 17
ReDim vntData(0, 0 To .ColumnCount - 1)
.List = vntData
For Each rCl In rlist
'.AddItem rCl.Value
For i = 1 To Range("rCl").Rows.Count
AddItem Cells(i, 1).Value
.List(.ListCount - 1, 1) = Cells(i, 2)
.List(.ListCount - 1, 2) = Cells(i, 3)
.List(.ListCount - 1, 3) = Cells(i, 4)
.List(.ListCount - 1, 4) = Cells(i, 5)
.List(.ListCount - 1, 5) = Cells(i, 6)
.List(.ListCount - 1, 6) = Cells(i, 7)
.List(.ListCount - 1, 7) = Cells(i, 8)
.List(.ListCount - 1, 8) = Cells(i, 9)
.List(.ListCount - 1, 9) = Cells(i, 10)
.List(.ListCount - 1, 10) = Cells(i, 11)
.List(.ListCount - 1, 11) = Cells(i, 12)
.List(.ListCount - 1, 12) = Cells(i, 13)
.List(.ListCount - 1, 13) = Cells(i, 14)
.List(.ListCount - 1, 14) = Cells(i, 15)
.List(.ListCount - 1, 15) = Cells(i, 16)
.List(.ListCount - 1, 16) = Cells(i, 17)
Next i
'setting the column width to auto size
For i = 1 To .ColumnCount
With Me.Controls.Add("Forms.ListBox1", Name:="txtTemp" & i)
.AutoSize = True
.MultiLine = True
.WordWrap = False
.SelectionMargin = False
With .Font
.Name = ListBox1.Font.Name
.Size = ListBox1.Font.Size
End With
End With
Next i
For i = 0 To .ListCount - 1
Me.Controls("txtTemp1").Text = Me.Controls("txtTemp1").Text & vbCr & .List(i, 0)
Me.Controls("txtTemp2").Text = Me.Controls("txtTemp2").Text & vbCr & .List(i, 1)
Me.Controls("txtTemp3").Text = Me.Controls("txtTemp3").Text & vbCr & .List(i, 2)
Me.Controls("txtTemp4").Text = Me.Controls("txtTemp4").Text & vbCr & .List(i, 3)
Me.Controls("txtTemp5").Text = Me.Controls("txtTemp5").Text & vbCr & .List(i, 4)
Me.Controls("txtTemp6").Text = Me.Controls("txtTemp6").Text & vbCr & .List(i, 5)
Me.Controls("txtTemp7").Text = Me.Controls("txtTemp7").Text & vbCr & .List(i, 6)
Me.Controls("txtTemp8").Text = Me.Controls("txtTemp8").Text & vbCr & .List(i, 7)
Me.Controls("txtTemp9").Text = Me.Controls("txtTemp9").Text & vbCr & .List(i, 8)
Me.Controls("txtTemp10").Text = Me.Controls("txtTemp9").Text & vbCr & .List(i, 9)
Me.Controls("txtTemp11").Text = Me.Controls("txtTemp9").Text & vbCr & .List(i, 10)
Me.Controls("txtTemp12").Text = Me.Controls("txtTemp9").Text & vbCr & .List(i, 11)
Me.Controls("txtTemp13").Text = Me.Controls("txtTemp9").Text & vbCr & .List(i, 12)
Me.Controls("txtTemp14").Text = Me.Controls("txtTemp9").Text & vbCr & .List(i, 13)
Me.Controls("txtTemp15").Text = Me.Controls("txtTemp9").Text & vbCr & .List(i, 14)
Me.Controls("txtTemp16").Text = Me.Controls("txtTemp9").Text & vbCr & .List(i, 15)
Me.Controls("txtTemp17").Text = Me.Controls("txtTemp9").Text & vbCr & .List(i, 16)
Next i
For i = 1 To .ColumnCount
strCWidths = strCWidths & Me.Controls("txtTemp" & i).Width & ";"
lngTotalWidth = lngTotalWidth + Me.Controls("txtTemp" & i).Width
Me.Controls("txtTemp" & i).Visible = False
Me.Controls.Remove ("txtTemp" & i)
Next i
.Width = lngTotalWidth + txtTemp.ColumnCount + 5
.ColumnWidths = strCWidths

Next rCl