Unique sorted list form combo box refresh

victorel21

New Member
Joined
Jul 8, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Hi, I am making a combo box that allows the user to delete rows based on data in columns.

I have a code that prepares the combo box below it creates a list of unique values from a column. Problem is once I delete the lines and hide the form, when I open the form again a blank option appears and the first letter doesnt show. Even if I dont delete anything just rerunning the code creates the error. I have uploaded images.

VBA Code:
Sub prepare_delete_by_status()

Set Wks = ThisWorkbook.Worksheets("Classification")
       
        Set RngBeg = Wks.Range("C2")
        col = RngBeg.Column
       
        Set RngEnd = Wks.Cells(Rows.Count, col).End(xlUp)
       
            Set Entries = New Collection
            ReDim Items(0)
           
            For row = RngBeg.row To RngEnd.row
                Set cell = Wks.Cells(row, col)
                    On Error Resume Next
                        test = Entries(cell.Text)
                        If Err = 5 Then
                            Entries.Add index, cell.Text
                            Items(index) = cell.Text
                            index = index + 1
                            ReDim Preserve Items(index)
                        End If
                    On Error GoTo 0
            Next row
             
        index = index - 1
        Descending = False ' Set this to True to sort in descending order Z-A.
        ReDim Preserve Items(index)
       
            Do
                Sorted = True
               
                For j = 0 To index - 1
                    If Descending Xor StrComp(Items(j), Items(j + 1), vbTextCompare) = 1 Then
                        temp = Items(j + 1)
                        Items(j + 1) = Items(j)
                        Items(j) = temp
                       
                        Sorted = False
                    End If
                Next j
               
                index = index - 1
            Loop Until Sorted Or index < 1
                  
        Form4.ComboBox1.List = Items

  Form4.ComboBox1.Text = Items(0)
 
x = Form4.ComboBox1

Sheets("Classification").Activate

    With Sheets("Classification")

            Form4.TextBox2 = Cells(.Range("C:C").Find(x, Lookat:=xlWhole).row, 4)
           
    End With
   
    With WorksheetFunction
   
    Form4.TextBox3 = Format(.CountIfs(Sheets("Classification").Range("C:C"), x), "#,##0.00")
    Form4.TextBox4 = Format(.SumIfs(Sheets("Classification").Range("G:G"), Sheets("Classification").Range("C:C"), x), "#,##0.00")
    Form4.TextBox5 = Format(.SumIfs(Sheets("Classification").Range("H:H"), Sheets("Classification").Range("C:C"), x), "#,##0.00")
   
    End With
   
End Sub
 

Attachments

  • Unique list error.jpg
    Unique list error.jpg
    118 KB · Views: 14
Last edited by a moderator:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,624
Latest member
gregg777

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top