Formatting of listbox columns - 10000+ rows, without slowing the runtime

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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

Thanks mehmetcik for your reply.

I do not know how to use this method : "Use Row 0 as a Headline, Put your formatting information there, Select Row 0 and it will be highlighted Blue."
Please give some more info on this.

Also the code given by you: Me.lbCustLog.Range("A5").CurrentRegion - this gives error.
Isn't .range("A5").CurrentRegion a property of worksheet and not a listbox?
Please guide me know if I missed anything here.

Thanks mehmetcik. Now I understand what you meant by put formating info in Heading row(0). However, that's not what I want. I really needed to format the listcolumns related to amounts and dates as per the local date formats. Thanks for your help.

Thanks Mike. I looped thru the array and formatted the required elements, and it was really fast.

Second question: How do I format the columns displaying amounts as right aligned. As of now the list columns with numbers are formatted with below code and they appear left aligned.

vSL(i, LServFeeCol) = Format(vSL(i, LServFeeCol), "#,##0")

Thanks for taking time to look at the point of 10,000+ rows in listbox. I will explain the process. So far its working well. Pls let me know if I can improve the process.

The transaction data is stored in a sheet table - 10,000+ rows. It gets populated in an array(main Array) first by assigning DataBodyRange of the table and then in the listbox.

On top of the list box I have a few comboboxes which act as filters for this data. Like Customer name, Service Category, Service Code, DueMonth, and so on. Everytime a combobox value changes(the user applies one or more filter), I use advanced filtercopy method and paste the filtered table in another sheet. That filtered table is then stored in another array(Filtered Array) and it populates the listbox. A user will typically apply 2-3 filters and the number of items in listbox will reduce to 10 - 100. In fact it can reduce to even 1 if correct filters are applied. I found this(Adv filtercopy) to be the fastest method to lookup the original data table and bring in filtered data in listbox.

The reason for two arrays: The first array is used for the top section of the userform where I display yearly transaction summary values. The filtered array is used to populate listbox based on combobox values.

Once the user gets hold of the relevant entries - by applying correct filters, necessary actions can be taken for those entries - like assign an executive, update status, generate invoice, etc. At that point I write back the updates to the original data table and refresh both the arrays.

Do let me know if I can improve this process.