2 issues, appreciate your help: Q1: on user selection of rep name from col CF need to select new cell ref in col CG. code below is not working, fails on worksheetfunction, am open to alternate/simpler method. Q2: in col CF list of rep names I have 'future use' blank cells, I don't want those to show in combobox.

Private Sub ComboBox2_Change()

Dim varRepName As String
Dim varRepHome As String

varRepName = ComboBox2.Text
varRepHome = WorksheetFunction.Lookup(varRepName, "CF599:CF637", "CG599:CG637")  ' get range from 'right' rep names

    Select Case ComboBox2.Text
        Case varRepName
            Range(varRepHome).Select  ' reposition screen to new range
            ActiveWindow.ScrollColumn = ActiveCell.Column  ' part 1 positions _ above cell as 'A1'
            ActiveWindow.ScrollRow = ActiveCell.Row  ' part 2 positions above _ cell as 'A1'

        Case Else
    End Select

    ComboBox2.Value = ""  ' reset value to null so combobox is blank when _ needed again    

End Sub