combobox issues

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

Q1:

varRepHome = WorksheetFunction.Lookup(varRepName, _
    Range("CF599:CF637"), Range("CG599:CG637") )

Here's what I came up with. Let me know if you run into any problems.

Private Sub ComboBox2_Change()
    'If ComboBox2 is empty then do NOT execute code
    If ComboBox2.Value = "" Then Exit Sub
    
    'ComboBox2 value is not blank, code continues
    
    'Make sure ComboBox2 value is found in the range "CF599:CF637"
    'When ComboBox2 value is found in the range exit For Loop
    'Define variable "x"
    For Each rng In Range("CF599:CF637")
        If rng = ComboBox2.Value Then
            rng.Offset(0, 1).Select
            x = 1
            Exit For
        End If
    Next rng
        
    'Reset ComboBox2 value to null
    ComboBox2.Value = ""
    
    'If the ComboBox2 value is not found in the range
    'then variable "x" will not be equal to 1
    'Therefore do not reposition view
    If x <> 1 Then Exit Sub
    
    '"x" equals 1 so a value was found, code continues
    
    'Position view according to ActiveCell
    ActiveWindow.ScrollColumn = ActiveCell.Column
    ActiveWindow.ScrollRow = ActiveCell.Row

End Sub

Private Sub Worksheet_Activate()
    'Clear ComboBox2 upon activation
    ComboBox2.Clear
    
    'Repopulate ComboBox2 without empty cells
    For Each rng In Range("CF599:CF637")
        If rng <> "" Then ComboBox2.AddItem rng
    Next rng
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    'Clear ComboBox2 upon any changes to worksheet
    ComboBox2.Clear
    
    'Repopulate ComboBox2 without empty cells
    For Each rng In Range("CF599:CF637")
        If rng <> "" Then ComboBox2.AddItem rng
    Next rng
End Sub