make faster code after increase data for 4000 rows for each sheet on userform

[abdelfattah]

Hello
I need improving the code . it becomes slow when search by combobox on userform . the data became 4000 rows for each sheet .
the code will populate data in listbox when call from macro LBoxPop based on sheet selection from combobox1

Option Explicit
Option Compare Text

Private Data, Temp, Crit As String, i As Long, lr As Long, ii As Long, x As Long
Dim ws As Worksheet



Private Sub ComboBox1_Change()
'ActiveSheet.Visible = True
If ComboBox1.Value <> "" Or ComboBox2.Value <> "" Then OptionButton1.Value = False: OptionButton2.Value = False
 If ComboBox1.Value <> "" And ComboBox2.Value <> "" Then
  TextBox1.Value = ""
  TextBox2.Value = ""
  TextBox3.Value = ""
  
  CommandButton1.Enabled = True
  
  End If
  If ComboBox1.Value = "" Then ListBox1.Clear
  If ComboBox1.Value = "" Then TextBox1.Value = "": TextBox2.Value = "": TextBox3.Value = ""
  If ComboBox1.Value <> "" Or ComboBox2.Value = "" Then TextBox4.Visible = False: TextBox5.Visible = False: TextBox6.Visible = False: _
  TextBox1.Visible = True: TextBox2.Visible = True: TextBox3.Visible = True
  
  If ComboBox1.Value = "" Then Exit Sub

Set ws = Sheets(ComboBox1.Value)

ws.Activate

With ws
lr = .Range("A" & Rows.Count).End(xlUp).Row
TextBox1.Value = .Range("C" & lr).Value
TextBox2.Value = .Range("D" & lr).Value
TextBox3.Value = .Range("E" & lr).Value
If TextBox3.Value < 0 Then
TextBox3.ForeColor = vbRed
Else: TextBox3.ForeColor = vbBlack
End If
End With

  Call LBoxPop

  
 

End Sub

[CODE=vba]
Private Sub LBoxPop()
    Dim r          As Long, C As Long
    Dim Data()     As Variant
    Dim rng        As Range
    
     
   
    Set rng = ws.Cells(1, 1).CurrentRegion
    ReDim Data(1 To rng.Rows.Count, 1 To rng.Columns.Count + 1)
 
    For r = 1 To UBound(Data, xlRows)
        For C = 1 To UBound(Data, xlColumns)
            Data(r, C) = rng.Cells(r, C).Text
        Next C
    Next r
 
    With UserForm1.ListBox1
        .ColumnCount = 5
        .columnWidths = "80;335;100;100;100"
        .List = Data
    End With
    
        For i = ListBox1.ListCount - 1 To 0 Step -1

    Debug.Print i, ListBox1.List(i, 0)
      If ListBox1.List(i, 0) <> "" Then
        ListBox1.ListIndex = i
        Exit For
      End If
    Next i
  'End With
 


 
End Sub

thanks

 

[LongToast]

I work with bunch of data too, I solve the slow code by use pagination, with pagination you don't need to load much data at one time, see picture below :

 

[abdelfattah]

what's that?!
I don't understand you !

 

[Akuini]

    For r = 1 To UBound(Data, xlRows)
        For C = 1 To UBound(Data, xlColumns)
            Data(r, C) = rng.Cells(r, C).Text
        Next C
    Next r

Looping through the cells directly is slow.
Why do you use .Text instead of .Value?
Could you provide a sample data?
OR
Could you please upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here? Also, ensure that the link is accessible to anyone.

 

[abdelfattah]

OR
Could you please upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here? Also, ensure that the link is accessible to anyone.

I will see what I can do today .
thank you

 

[abdelfattah]

here is sample simple with exclude DATA,MAIN sheets
file.xlsm
and my real file will be at least 20 sheets , every sheet contains 2500 rows at least and will increase for about 4000 rows as maximum
I hope you have free time to find better code than what I have

thanks in advance

 

[Akuini]

I've downloaded your file.
Questions:
1. Why do you use .Text instead of .Value? in this part:

    For r = 1 To UBound(Data, xlRows)
        For C = 1 To UBound(Data, xlColumns)
            Data(r, C) = rng.Cells(r, C).Text
        Next C
    Next r

2. Why do you need to load cell interior color to Data in this part:

Data(r, 6) = rng.Cells(r, 5).Interior.Color

3. What is the purpose of the listbox? to update data in the sheets?

 

[abdelfattah]

1. Why do you use .Text instead of .Value? in this part:

I got for modification by another body , but I supposes to populate the same formatting as in existed in sheet.

2. Why do you need to load cell interior color to Data in this part:

it was to populate last cell has red color because I wanted to highlight the last cell is matched with balance is existed in another file . you can ignore it .

3. What is the purpose of the listbox? to update data in the sheets?

I'm not sure why ask for it ?
you mean the listbox is useless ?
I prefer it instead of move from sheet to another manually to see it data. I think the listbox with combobox is fast to search for data moreover I don't prefer working from inside the sheet.

 

[abdelfattah]

Hi Akuini
did you read my last comment ?

 

[Akuini]

Sorry for the late reply.
Try it like this:

Private Sub LBoxPop()
    Dim r          As Long, c As Long
    Dim Data()     As Variant
    Dim rng        As Range
    Dim va
     
   Debug.Print ws.Name
    Set rng = ws.Cells(1, 1).CurrentRegion
'    ReDim Data(1 To rng.Rows.Count, 1 To rng.Columns.Count + 1)
    Data = ws.Cells(1, 1).CurrentRegion.Value
    
    For i = 1 To UBound(Data, 1)
        Data(i, 1) = Format(Data(i, 1), "yyyy-mm-dd")
    Next
    
'    For i = 1 To UBound(Data, 1)
'        Data(i, 3) = Format(Data(i, 3), "0.00")
'        Data(i, 4) = Format(Data(i, 4), "0.00")
'        Data(i, 5) = Format(Data(i, 5), "0.00")
'    Next
'
    
     Me.ListBox1.List = Data
 
    With UserForm1.ListBox1
        .ColumnCount = 5
        .columnWidths = "90;300;120;120;100"
        .List = Data
    End With
 
 
End Sub