Userform Text Input slows down

[Guard913]

So I am pretty sure I know why it is slowing down, and my hope is to find out how to speed it back up without changing the excel table info, and I have no control over changing it.

Works Fine (Good Speed)

Private Sub CboOfferCode_Change()
Dim ws As Worksheet
Dim x, dict
Dim i As Long
Dim str As String
Set ws = Sheets("Lists")
x = ws.Range("Offer_Code").Value
Set dict = CreateObject("Scripting.Dictionary")
str = Me.CboOfferCode.Value
If str <> "" Then
    For i = 1 To UBound(x, 1)
        If InStr(LCase(x(i, 1)), LCase(str)) > 0 Then
            dict.Item(x(i, 1)) = ""
        End If
    Next i
    Me.CboOfferCode.List = dict.keys
Else
    Me.CboOfferCode.List = x
End If
Me.CboOfferCode.DropDown
Worksheets("Prom Codes").Range("H2") = Me.CboOfferCode.Value

    With Sheets("Prom Codes")
 
    .Range("A1:E507").AutoFilter Field:=1, Criteria1:=.Range("H2").Value
 
    End With
    
     CommandButton12.Enabled = True
End Sub

Takes time for text to show after typing

Private Sub CboItem_Change()
Dim ws As Worksheet
Dim x, dict
Dim i As Long
Dim str As String
Set ws = Sheets("Lists")
x = ws.Range("Item").Value
Set dict = CreateObject("Scripting.Dictionary")
str = Me.CboItem.Value
If str <> "" Then
    For i = 1 To UBound(x, 1)
        If InStr(LCase(x(i, 1)), LCase(str)) > 0 Then
            dict.Item(x(i, 1)) = ""
        End If
    Next i
    Me.CboItem.List = dict.keys
Else
    Me.CboItem.List = x
End If
Me.CboItem.DropDown
Worksheets("Rates").Range("N24") = Me.CboItem.Value

    With Sheets("Rates")
 
    .Range("A1:L280242").AutoFilter Field:=2, Criteria1:=.Range("N24").Value
 
    End With

CboTerm.Enabled = True

End Sub

I am almost certain that this part is causing it: (Because of how big the range is)
(And I have no control of size of Range so everything I do has to work with what is on hand, I can only control how the Userforms are set up.)

     With Sheets("Rates")
 
    .Range("A1:L280242").AutoFilter Field:=2, Criteria1:=.Range("N24").Value
 
    End With

And this one works fine because it has a much lower range to contend with.

    With Sheets("Prom Codes")
 
    .Range("A1:E507").AutoFilter Field:=1, Criteria1:=.Range("H2").Value
 
    End With

 

[VBE313]

how about declaring a Long variable like lastRow?

Sub hi()
Dim lastRow As Long
    With Sheets("Rates")
        lastRow = .Cells(.Rows.Count, "L").End(xlUp).Row
        .Range("A1:L" & lastRow).AutoFilter Field:=2, Criteria1:=.Range("N24").Value
    End With
    With Sheets("Prom Codes")
        lastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
        .Range("A1:E" & lastRow).AutoFilter Field:=1, Criteria1:=.Range("H2").Value
    End With
End Sub

 

[Guard913]

how about declaring a Long variable like lastRow?

Sub hi()
Dim lastRow As Long
    With Sheets("Rates")
        lastRow = .Cells(.Rows.Count, "L").End(xlUp).Row
        .Range("A1:L" & lastRow).AutoFilter Field:=2, Criteria1:=.Range("N24").Value
    End With
    With Sheets("Prom Codes")
        lastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
        .Range("A1:E" & lastRow).AutoFilter Field:=1, Criteria1:=.Range("H2").Value
    End With
End Sub

Thank you so much for your help!!!

Good News & Not So Good News
1st your code does work (ie it doesn't break my form), sadly I can barely even tell a difference in speed, but it might have sped up a little bit. Any other Ideas? I am here for the next 5 hours lol....

I really wish the people who built the table didn't add so many duplicates.... I checked in a seperate file over 9000 rows of duplicated items... But no.... they don't want me to change that.... lol

 

[VBE313]

Can you tell me what your code is doing?

 

[Guard913]

Can you tell me what your code is doing?

It uses a combobox drop down to allow me to search list, and then it puts combobox value into excel sheet which then filters appropriate column (long range), I then do the same for the rest of the items.

However all the boxes that deal with the long range has this issue. So the hope will be once 1 is fixed I'll just replicate it on the others.

The trick is getting each 1 to remember the filter done before it so that you end up with the shortest results as possible. Course since they duplicate...triplicated things spent 2 hours last night finding a way to just pull the first 3 rows found into userform!! With great help from this site!!

Image provided shows the form in action, and how they excel sheet looks after auto filter occurs.

Thanks!!!

 

[Guard913]

Can you tell me what your code is doing?

Fixed it, took out the auto filter and put that on its on on exit!!! So first part runs smoothly, 2nd part runs fine as well!!!