Combo Box Index select

[av8tordude]

I have a userform that loads symbols in a combo box from column A that do not have a date listed in column D. When I select the symbol from the combo box, I would like to select the row corresponding to my selection. Can someone assist with a code that can achieve this.

Thank you kindly

[mana]

Private Sub cbName_Change()

If cbName.ListIndex > -1 Then
With Sheets("sheet1").Range("a5").CurrentRegion
.AutoFilter
.AutoFilter 1, cbName.Value
.AutoFilter 4, "="
End With
End If


End Sub

[av8tordude]

Hi Mana....The code is filtering. Can you code it to only select the row?

Thank you for you kind help

[mana]

Option Explicit

Private Sub UserForm_Initialize()
Dim r As Range, c As Range
Dim a()
Dim n As Long

On Error Resume Next
Set r = Sheets("sheet1").Range("a6").CurrentRegion.Columns(4).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If r Is Nothing Then Exit Sub

ReDim a(r.Count - 1, 1)

For Each c In r
a(n, 0) = c.Offset(, -3).Value
a(n, 1) = c.Address(, , , True)
n = n + 1
Next

With cbName
.ColumnCount = 2
.BoundColumn = 1
.TextAlign = fmTextAlignLeft
.ColumnWidths = "30;0"
.List() = a()
End With

End Sub


'
Private Sub cbName_Change()

If cbName.ListIndex > -1 Then
Application.Goto Range(cbName.List(cbName.ListIndex, 1))
End If

End Sub

[av8tordude]

Thank you Mana...Is there a way for it to select the cell in column A once it finds the selection?

[mana]

ReDim a(r.Count - 1, 1)

For Each c In r.Offset(, -3)
a(n, 0) = c.Value
a(n, 1) = c.Address(, , , True)
n = n + 1
Next

[av8tordude]

I have data in rows 1 - 4 and a header in Row 5, How can I account for this. Because I have 4 spaces in the combo box preceding the list

[mana]

On Error Resume Next
Set r = Sheets("sheet1").Range("a6")
Set r = Range(r, r.End(xlDown))
Set r = r.Columns(4).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

[av8tordude]

Perfect...Thank you very much for you patience, understanding, and expertise.

Cheers

[snb]

What did you learn from this thread ?

[av8tordude]

Digesting the variables. I'm using F8 to step through the code to understand what's happening.

[snb]

I didn't see any VBA code from your side.

[snb]

As Mana showed you: the userform is 100% redundant.


Private Sub Workbook_Open()
Sheet1.Cells(6, 1).CurrentRegion.AutoFilter 4, "="
End Sub