Pokud data stažená z databáze přesahují rozsah pole se seznamem, nové položky se jednoduše nezobrazí. Chcete-li tomu čelit, rozsah, který je základem seznamu nebo rozbalovacích polí, je třeba rozbalit nebo zkrátit, aby odpovídal datům. Tento článek zkoumá, jak to provést automaticky.
Předpokládá se, že čtenář má zobrazenou pásku Developer a je obeznámen s editorem VBA. Pokud ne, prosím Google „Excel Developer tab“ nebo „Excel Code Window“.
Profesionální způsob zobrazení rozevíracího seznamu je, aby se jejich rozsahy podle potřeby rozšiřovaly nebo stahovaly. Například:
Klíčem k dynamickému rozsahu je sledování počtu naplněných řádků v příslušném sloupci pomocí funkce = countA. Tato funkce počítá naplněné prvky v sekvenci buněk, dokud nenarazí na poslední; v případě prvního diagramu na obrázku výše by to byl řádek 11.
Chcete-li automaticky udržovat rozsah, vyžaduje definované názvy ke sledování počtu naplněných řádků. Například používáme eCol (koncový sloupec) a eRow (koncový řádek) k definování hranic našeho rozsahu. Čím více řádků naplníme, tím větší bude hodnota eRow.
Definované názvy výše nastavily seznam na jeden sloupec široký (eCol = 1) podle počtu naplněných řádků v eCol (eRow = 11)
Nakonec se tituly objeví v rozsahu „A2: A“ & eRow. Všimněte si, že funkce Index se používá k vytvoření poslední buňky v rozsahu zvaném „Tituly“. Rozsah „A2: A“ & eRow se v této fázi efektivně překládá na „A2: A11“.
Dynamický rozsah můžeme nastavit automaticky při otevření sešitu pomocí dílčí procedury Auto_open, která se spustí dříve, než se sešit stane viditelným.
Kodex
Otevřete sešit a naplňte jej seznamem a některými daty. Ukázkový sešit použitý v tomto cvičení najdete zde.
Otevřete okno kódu VBA a vložte modul. Zkopírujte níže uvedený kód do modulu.
Událost Auto_Open nastavuje hodnoty posledního řádku a posledního sloupce pro dynamický rozsah „Tituly“ a zaznamenává následné provedené změny.
Sub auto_open()
Dim eRow As Integer, eCol As Integer, i As Long
On Error Resume Next
'Clear the present define names, to avoid any duplications
activeworkbook.Names("eCol").Delete
activeworkbook.Names("eRow").Delete
activeworkbook.Names("Titles").Delete
Range("A1").Select
'Titles will appear in the first column, A in this case
eCol = 1
'Find the last populated row
eRow = Sheets("Main").Cells(Rows.Count, eCol).End(xlUp).Row
'Define the names
activeworkbook.Names.Add Name:="eCol", RefersTo:="=COUNTA($1:$1)"
activeworkbook.Names.Add Name:="eRow", RefersToR1C1:="=COUNTA(C" & ColNo & ")"
activeworkbook.Names.Add Name:="Titles", RefersTo:="=A2:INDEX($2:$200," & "eRow," & "eCol)"
End Sub
Sub DropDown1_Change()
MsgBox "Directed by " & Cells(2, 5)
End Sub
Poznámka: Vše bylo pro zjednodušení prohlížení umístěno na jedné stránce. Hodnoty ve sloupcích A a B a D a E by se obvykle nacházely na jiném, pravděpodobně skrytém listu. Upozorňujeme také, že druhý sloupec B se v tomto konkrétním cvičení v definicích rozsahu nepoužívá; hodnoty sloupce B se získávají obvyklým způsobem pomocí vlastnosti cell-link v rozbalovacím seznamu (D2 odráží výběr třetího prvku v rozsahu, který začíná v A2, ve spojení s funkcí Index v „E3“ pro nalezení ředitele ( =INDEX(B:B,D2+1,1)).
Uložte sešit a znovu jej otevřete. Pole se seznamem bude původně vyplněno Auto_open. Přidejte položky do sloupců A a B a sledujte změny v poli se seznamem.
Zachraňte poškozené soubory aplikace Excel
Po neočekávaném zhroucení aplikace Excel se čas od času mohou soubory Excel poškodit. Pokud máte zálohu, můžete jednoduše obnovit data pomocí zálohy. V opačném případě budete možná muset vyhledat profesionálního odborníka nebo nástroj k obnovení poškozený Excel soubory.
Úvod autora:
Felix Hooker je odborník na obnovu dat v oboru DataNumen, Inc., která je světovým lídrem v oblasti technologií pro obnovu dat, včetně oprava rar souborů a SQL softwarové produkty pro obnovu. Pro více informací navštivte www.datanumen.com