Snasui Excel Forum

Code: Select all

Private Sub ComboBox1_Change()
    Dim irRange As Range
    Dim lr As Long
            lr = Range("e" & Rows.Count).End(xlUp).Row
            Set irRange = .Range("e6:e" & lr)
            irRange.Borders.LineStyle = xlContinuous
            irRange.Sort Key1:=.Range("e6"), Order1:=xlAscending, Header:=xlGuess
End Sub

Code: Select all

Private Sub ComboBox1_Change()
Dim ra As Range, r As Range
Dim lr As Long
lr = Range("E" & Rows.Count).End(xlUp).Row
With Range("E6:E" & lr)
    .ClearContents
    .Borders.LineStyle = xlNone
End With
With Sheets("Sheet1")
    Set ra = .Range("I5", .Range("I" & Rows.Count).End(xlUp))
End With
For Each r In ra
    If r = Sheets("Sheet1").OLEObjects("ComboBox1").Object.Text Then
        Sheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Offset(1, 0) = r.Offset(0, -1)
    End If
Next r
lr = Range("E" & Rows.Count).End(xlUp).Row
With Range("E6:E" & lr)
    .Borders.LineStyle = xlContinuous
End With
End Sub