I have a Pivot table that has a list of items.
This list is produced from a Categories column
As more categories are added, this pivottable gets longer
I have a combobox that shows all of the items in this Pivottable
Currently this is the code I use for the combobox list:
Me.CmboCategory.List = Worksheets("1 parts").Range("AB2:AB25").Value
I would like to make the Range("AB2:AB25").Value change based on the exact length of the pivot table
so it would go from Range("AB2:AB25").Value to Range("AB2:AB26").Value ect ect... depending on how many other categories are added later on.
I thought I could cheat and just make a string:
listrng = "Worksheets(""1 parts"").Range(" & """AB2:" & "AB" & Range("ac2").Value & """).value"
In which produces: Worksheets("1 parts").Range("AB2:AB23").value
Then use Me.CmboCategory.List = listrng
But that doesnt work.
Basically I want the list length of my combobox to be the exact number of items in my pivottable
Thanks in advance!
This list is produced from a Categories column
As more categories are added, this pivottable gets longer
I have a combobox that shows all of the items in this Pivottable
Currently this is the code I use for the combobox list:
Me.CmboCategory.List = Worksheets("1 parts").Range("AB2:AB25").Value
I would like to make the Range("AB2:AB25").Value change based on the exact length of the pivot table
so it would go from Range("AB2:AB25").Value to Range("AB2:AB26").Value ect ect... depending on how many other categories are added later on.
I thought I could cheat and just make a string:
listrng = "Worksheets(""1 parts"").Range(" & """AB2:" & "AB" & Range("ac2").Value & """).value"
In which produces: Worksheets("1 parts").Range("AB2:AB23").value
Then use Me.CmboCategory.List = listrng
But that doesnt work.
Basically I want the list length of my combobox to be the exact number of items in my pivottable
Thanks in advance!