Creating filtered list for Dropdown box

[haplc]

Dear All,

I have two dropdwons: CVolt and CPilot.
On change of value in CVolt, it is opening a sheet (in the same excel workbook) "Pilot" and filtering coloumn 4 using value from the dropwndown value

Result should be that the list of drowndown "Cpilot" should contain the row fitlered using CVolt value.
It is working good but issue is: I am seeing the list if all the filtered rows are one after other. i.e. if first 4 row contains same criteria then they all four rows are visibile as the list of CPilot.
But if the rows are located at lets say in row # 1, 2 and then 6, 7, I am able to see only row 1 & 2 as list of Cpilot and Iam not able to see row 6 &7 (despite these rows are also having same value as row 1 &2.)
In the excel sheet "Pilot", I am able to see all rows 1, 2, 6 and 7.

Here is code for "on change" of dropdown "CVolt":

' clearing existing fillter​

ThisWorkbook.Worksheets("Pilot").Range("a1:d1").AutoFilter Field:=4, Criteria1:="*", visibledropdown:=True

' setting filter based on the value selected in CVolt dropdown​

ThisWorkbook.Worksheets("Pilot").Range("a1:d1").AutoFilter Field:=4, Criteria1:=Myform.CCVolt.Value, visibledropdown:=True

' setting list of dropdown CPilot using filtered value set by CVolt from excel sheet "Pilot"​

Myform.CPilot.List = Worksheets("Pilot").Range("a2:b" & (ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row)).SpecialCells(xlCellTypeVisible).Value

thanking you in advance
Best regards