Add New Item in ComboBox in a Cool Way

Step 1: In any column on a sheet let's assume Column "J". in Cell J1 type header of the combo box and in Cell J2 type "Add New Item"

Step 2: Select the Cell J2 --> Goto Formulas Tab --> Click on Name Manager --> Click on "New" --> Type any name you required, Let's say you typed "MY LIST"

=OFFSET(KDATA!$J$2,0,0,COUNTA(KDATA!$J:$J)-1,1)

Step 3: Now, U can design the user form and add the combo box in it. type the following line in the userform_initialize event

Userform1.ComboBox1.RowSource = "MY LIST"

Step 4: Double click on the combo box and write following VBA codes into it. and that's it.

Private Sub D3_Change()

    If U.D3.Value = "Create New Type" Then
        With shKDATA
            .Range("J" & .Cells(.Rows.Count, "J").End(xlUp).Row + 1).Value = _
            Application.InputBox("Please Type New Supplier Type", "Add New Supplier Type")
            With .Range("J" & .Cells(.Rows.Count, "J").End(xlUp).Row)
                If .Value = False Then
                    .Value = vbNullString
                End If
                U.D3.RowSource = "NEW_SUP_TYPE"
                U.D3.Value = .Value
            End With
        End With
    End If

End Sub

U = Name of Userform1
D3 = Name of ComboBox1

- Kamal Bharakhda

Look for the String in the Cell of Multiple Column Table and if not found then Filter the whole row

Suppose, You have 10 columns table in excel and want to filter out such rows which do not have String that you are looking for. If any cell of the row has such string then it should not be filtered out.