Excel VBA- Dropdown

EXCEL VBA: LOAD DATA FROM A SHEET INTO DROPDOWN OBJECT

Conditions: 1) Data should get loaded from Sheet1 into ComboBox but missing values should be excluded
2) The output should be populated in the Sheet2 where the ComboBox is. Data should get populated based on combo box selection, but shouldn't get cleared, and get added below, as per selection from the combobox

DATA AT SHEET1

ID	Name	Data
1	Anuja	10
2	Kumud	87
3	Ajeet	23
4	Ramesh	90
5	Sachin	45
6	Abhinav	56
7	Saurabh	77
11	Mahmood	67
12	Priyanka
111	Gaurav	33

COMBOBOX AT SHEET2

Option Explicit

Sub cboDropDownPersons_Change()

    Dim cboPersons As Object

    Dim lngR As Long

    Dim lngPersonIndex As Long

    Dim varResult As Variant

    Dim varIdValue

    Dim varDataValue

    Dim lngFR1 As Long, lngFR2 As Long, lngMaxValue As Long

    Dim rngDestCell As Range

    On Error GoTo Errhandler1

    

    Set cboPersons = shtOutput.DropDowns("cboDropDownPersons")

    lngPersonIndex = cboPersons.Value

    varResult = fDictionaryData(lngPersonIndex)

    varIdValue = Left(varResult, InStr(varResult, "^") - 1)

    varDataValue = Right(varResult, Len(varResult) - InStr(varResult, "~"))

    lngFR1 = shtOutput.Range("B" & Rows.Count).End(xlUp).Row + 1

    lngFR2 = shtOutput.Range("C" & Rows.Count).End(xlUp).Row + 1

    lngMaxValue = WorksheetFunction.Max(lngFR1, lngFR2)

    shtOutput.Cells(lngMaxValue, 2) = varIdValue

    shtOutput.Cells(lngMaxValue, 3) = varDataValue

 

GoTo lblExit

Errhandler1:

pErrHandler

lblExit:

End Sub

Sub pLoadData()

        Dim cboPersons As Object

        Dim varData As Variant

        Dim lngLastR As Long

        Dim lngR As Long

        On Error GoTo ErrorHand2

               

        Set cboPersons = shtOutput.DropDowns("cboDropDownPersons")

                      

        lngLastR = shtInput.Cells(Rows.Count, 2).End(xlUp).Row

        varData = shtInput.Range("B2:B" & lngLastR)

        cboPersons.RemoveAllItems

        

        For lngR = LBound(varData) To UBound(varData)

            If Not IsEmpty(varData(lngR, 1)) Then

                cboPersons.List(lngR) = varData(lngR, 1)

            End If

        Next

GoTo lblExit

ErrorHand2:

pErrHandler

lblExit:

End Sub

Function fDictionaryData(varKey As Long) As String

        Dim objDixnry As Object

        Dim varData As Variant

        Dim rngData As Range

        Dim rngStart As Range

        Dim lngLastR As Long

        Dim lngR As Long

        Dim varky As Variant

        Dim varArray As Variant

        On Error GoTo ErrorHnd

        

        Set objDixnry = CreateObject("Scripting.Dictionary")

        Set rngStart = shtInput.Range("A2")

        Set rngData = rngStart.Resize(rngStart.Offset(100000).End(xlUp).Row - rngStart.Row + 1, rngStart.Offset(, 1000).End(xlToLeft).Column - rngStart.Column + 1)

        varData = rngData

        

        For lngR = LBound(varData) To UBound(varData)

            If Not IsEmpty(varData(lngR, 2)) Then

                   objDixnry.Item(varData(lngR, 1) & "^" & varData(lngR, 2) & "~" & varData(lngR, 3)) = varData(lngR, 1) & "^" & varData(lngR, 2) & "~" & varData(lngR, 3)

            End If

        Next

        

        varArray = objDixnry.items

        fDictionaryData = varArray(varKey - 1)

GoTo lblExit

ErrorHnd:

pErrHandler

lblExit:

End Function

Sub pErrHandler()

    MsgBox "Error Number: " & Err.Number & vbCrLf & "Description: " & Err.Description

End Sub