[Excel, VBA] Lege ruimtes validation list

[Sneezydevil]

Het Probleem:

Ik haal een aantal gegevens uit een access tabel, deze data zou ik vervolgens netjes in een validation keuze lijstje willen zetten.

Voor gebruiker 1 ziet dit dan bijvoorbeeld als volgt uit

data1

Leeg

data2

Leeg

data3

De plaatsen waar in mijn voorbeeld Leeg staat, staat in werkelijk heid ook niks en deze cellen zijn dus echt leeg. Maar nu zet hij deze lege cellen dus ook in me keuzelijst.

Wat heb ik geprobeerd:

Ik heb dit eerst proberen op te lossen door een vinkje te zetten bij "lege cellen negeren" in het instellingen menu van gegevensvalidatie, maar dit lijkt niet te werken.

Vervolgens dacht ik met de volgende regel in VBA

code:

Sheets("Extra").Range("A1:A100").SpecialCells(xlCellTypeBlanks).Delete

Deze lege ruimtes weg te halen, dit werkt wel maar dan krijg ik de volgende situatie:

data1

data2

data3

Leeg

Leeg

Dit is al beter maar bij de hoeveelheid gegevens die ik heb wordt de reeks lege cellen in mijn validation list best wel lang en zie je soms de werkelijke gegevens niet meer staan, verder brengt het nog met zich mee dat je in de lijst kunt invullen wat je wilt terwijl ik enkel uit de door mij ingevoerde gegevens zou willen kiezen.

Nu dacht ik dit weer op te lossen door in VBA de reeks van de keuzelijst aan te passen aan de hoeveelheid gevulde cellen.

code:

ThisWorkbook.Names.Add Name:="Keuzelijst", _
    RefersTo:="=Extra!$A$2:$A$100", Visible:=True

Bovenstaande code werkt natuurlijk alleen als het altijd een vaste reeks is, maar in mijn geval verschilt dit per gebruiker. Nu lukt het me wel te bepalen welke reeks ik moet hebben, maar het lukt niet dit mee te geven aan het RefersTo gedeelte van bovenstaande code.

code:

Dim Var As String
Var = "=Blad3!$A$2:$A$" + Eindereeks

Bovenstaande code mag dus schijnbaar niet onder VBA, waarbij Eindereeks een getal is.

Nu de vraag, waarom werkt het vinkje bij "Negeer lege velden" niet, en als dat dan ook niet gaat werken hoe voeg ik een variabele toe aan een string binnen VBA.

Of is er misschien een andere makkelijkere manier om dit op te lossen.

[onkl]

Een "name" heeft ook een ReferToRange, waarvoor je wel op een nette manier iets kan genereren.
Bijvoorbeeld, als kolom B leeg is (en je lijst in kolom A dus een "Region" is):

Visual Basic:

ThisWorkbook.Names.Add Name:="Keuzelijst"
ThisWorkbook.Names("Keuzelijst").ReferToRange=ThisWorkbook.Worksheets("Blad3").Range("A2").CurrentRegion

[Sneezydevil]

Doordat ik in de msdn naar rangerefersto ging zoeken kwam ik erachter dat mijn + natuurlijk een & moest zijn. Ik denk niet dat ik nu de mooiste oplossing heb maar het werkt in ieder geval.

[onkl]

Je bent geloof ik niet de enige die daaroverheen las. En ach, een oplossing is pas mooi als'ie werkt...