I have a dropdown on the ribbon and want to populate it by unique values from column B.
XML \[code\]<dropDown id="dd01" getItemCount="dd01Count" getItemLabel="dd01ListItem"/>\[/code\]VBA \[code\]Sub dd01Count(control As IRibbonControl, ByRef returnedVal)bk.Range("B1", bk.Range("B1").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=bk.Range("A1"), Unique:=TruereturnedVal = bk.Range("A1", bk.Range("A1").End(xlDown)).CountEnd SubSub dd01ListItem(control As IRibbonControl, index As Integer, ByRef returnedVal)bk.Range("B1", bk.Range("B1").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=bk.Range("A1"), Unique:=TruereturnedVal = bk.Range("A1", bk.Range("A1").End(xlDown)).Cells(index + 1, 1).ValueEnd Sub\[/code\]So, firstly I place unique values from ColumnB to ColumnA and then populate dd01 from ColumnA.
It works well if column B is without empty cells between, but if there are empy cells, I got various troubles (empty or missing items in dropdown).I also tried: \[code\]Range(bk.Range("B1"), bk.Range("B65536").End(xlUp)).AdvancedFilter... ' without success \[/code\]Is there an easy way to populate dropdown by unique (nonempty) cells values ?
XML \[code\]<dropDown id="dd01" getItemCount="dd01Count" getItemLabel="dd01ListItem"/>\[/code\]VBA \[code\]Sub dd01Count(control As IRibbonControl, ByRef returnedVal)bk.Range("B1", bk.Range("B1").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=bk.Range("A1"), Unique:=TruereturnedVal = bk.Range("A1", bk.Range("A1").End(xlDown)).CountEnd SubSub dd01ListItem(control As IRibbonControl, index As Integer, ByRef returnedVal)bk.Range("B1", bk.Range("B1").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=bk.Range("A1"), Unique:=TruereturnedVal = bk.Range("A1", bk.Range("A1").End(xlDown)).Cells(index + 1, 1).ValueEnd Sub\[/code\]So, firstly I place unique values from ColumnB to ColumnA and then populate dd01 from ColumnA.
It works well if column B is without empty cells between, but if there are empy cells, I got various troubles (empty or missing items in dropdown).I also tried: \[code\]Range(bk.Range("B1"), bk.Range("B65536").End(xlUp)).AdvancedFilter... ' without success \[/code\]Is there an easy way to populate dropdown by unique (nonempty) cells values ?