I have a listbox that is getting populated with transaction data: 10000+ rows and 25 columns
I had the below code to format 6 of the list columns. But that slows down the process drastically as well as gives some other problem(switches the screen from userform to the sheet). When I comment out this code the process runs faster and the other problem disappears.
For i = 0 To Me.lbCustLog.ListCount - 1
Me.lbCustLog.List(i, LServFeeCol - 1) = Format(Me.lbCustLog.List(i, LServFeeCol - 1), "#,##0")
Me.lbCustLog.List(i, LDueDtCol - 1) = Format(Me.lbCustLog.List(i, LDueDtCol - 1), "dd-Mmm-yyyy")
Me.lbCustLog.List(i, LStartDtCol - 1) = Format(Me.lbCustLog.List(i, LStartDtCol - 1), "dd-Mmm-yyyy")
Me.lbCustLog.List(i, LEndDtCol - 1) = Format(Me.lbCustLog.List(i, LEndDtCol - 1), "dd-Mmm-yyyy")
Me.lbCustLog.List(i, LPIDtCol - 1) = Format(Me.lbCustLog.List(i, LPIDtCol - 1), "dd-Mmm-yyyy")
Me.lbCustLog.List(i, LPaytDtCol - 1) = Format(Me.lbCustLog.List(i, LPaytDtCol - 1), "dd-Mmm-yyyy")
Next
How do I achieve the formatting of listbox columns without the above loop?
To give you some more details: The list box gets filled as follows:
I use Advanced filter copy method on a main data table and paste the filtered data (with exactly same columns) in another sheet
Then I add the filtered data to the array. The code is given below.
ServLogSheet.ListObjects("ServLogTbl").Range.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=ServLogFiltCritRng, CopyToRange:=ServLogFiltSheet.Range("A5").CurrentRegion
R = ServLogFiltSheet.Range("A5").CurrentRegion.Rows.Count
CountServ = R - 1
If R > 1 Then
vSL = ServLogFiltSheet.Range("A5").CurrentRegion.Offset(1).Resize(R - 1)
Else
Set vSL = Nothing
End If
'Clear and re-fill the listbox
Me.lbCustLog.Clear
With Me.lbCustLog
.ColumnCount = 22
.ColumnWidths = "35;0;55;100;0;55;55;0;200;55;55;55;55;55;55;0;0;55;0;0;0;0"
.ColumnHeads = False
If R > 1 Then
.List = vSL
Else
.Clear
End If
End With
Bookmarks