Så här justerar du kombinationsrutan eller listan automatiskt baserat på dynamiska dataintervall i Excel

Om data som laddats ner från en databas överstiger en kombinationsråds intervall visas de nya objekten helt enkelt inte. För att motverka detta måste intervallet som ligger till grund för List- eller kombinationsrutor utvidgas eller kontraktas för att matcha data. Den här artikeln undersöker hur man gör detta automatiskt. 

Det antas att läsaren har utvecklarbandet och är bekant med VBA Editor. Om inte, vänligen Google "Excel-fliken för utvecklare" eller "Excel-kodfönstret".

Ett professionellt sätt att visa kombinationsrutor är att få sina intervall att expandera eller krympa efter behov. Till exempel:Ett professionellt sätt att visa kombinationsrutor

Nyckeln till ett dynamiskt omfång övervakar antalet befolkade rader i relevant kolumn med funktionen = countA. Denna funktion räknar befolkade element i en sekvens av celler tills den träffar den sista; i fallet med det första diagrammet i bilden ovan skulle det vara rad 11.

För att behålla ett intervall krävs automatiskt definierade namn för att spåra antalet befolkade rader. Till exempel använder vi eCol (slutkolumn) och eRow (slutrad) för att definiera gränserna för vårt sortiment. Ju fler rader vi fyller, desto större blir eRows värde.Använd eCol och eRow för att definiera gränserna för räckvidd

De definierade namnen ovan har satt listan till en kolumn bred (eCol = 1) efter antalet befolkade rader i eCol (eRow = 11)

Slutligen visas titlarna i intervallet "A2: A" & eRow. Du kommer att notera att indexfunktionen används för att skapa den sista cellen i intervallet som heter "Titlar". Effektivt intervallet "A2: A" & eRow översätts till "A2: A11" i detta skede.Indexfunktion

Vi kan ställa in det dynamiska omfånget automatiskt när arbetsboken öppnas, genom att använda subproceduren Auto_open, som körs innan arbetsboken blir synlig.

Koden

Öppna en arbetsbok och fyll den i en kombinationsruta och lite data. Exempelarbetsboken som används i den här övningen finns här..

Öppna VBA-kodfönstret och sätt in en modul. Kopiera koden nedan till modulen.

Auto_Open-händelsen ställer in den sista raden och sista kolumnvärdena för det dynamiska området "Titlar" och noterar efterföljande ändringar.

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

Obs: Allt har placerats på en enda sida för att förenkla visningen. Vanligtvis skulle värdena i kolumnerna A och B, samt D och E, finnas på ett annat, eventuellt dolt, ark. Observera också att den andra kolumnen, B, inte används i definitionerna av området i den här specifika övningen; kolumn B:s värden erhålls på vanligt sätt via kombinationsrutans celllänksegenskap (D2 återspeglar valet av det tredje elementet i området, vilket börjar vid A2, i kombination med en indexfunktion i "E3" för att hitta direktören ( =INDEX(B:B, D2 + 1, 1)).Kombinationsrutan kommer initialt att fyllas av Auto_open

Spara arbetsboken och öppna den sedan igen. Kombinationsrutan kommer initialt att fyllas i av Auto_open. Lägg till objekt i kolumn A och B och observera ändringarna i kombinationsrutan.

Rädda skadade Excel -filer

Ibland kan Excel-filer skadas efter att Excel kraschar oväntat. Om du har en säkerhetskopia kan du helt enkelt återställa data med din säkerhetskopia. Annars kan du behöva söka en professionell expert eller ett verktyg för att återställa korrupt Excel filer.

Författarintroduktion:

Felix Hooker är en dataåterställningsexpert i DataNumen, Inc., som är världsledande inom teknik för återställning av data, inklusive rar-reparation och mjukvaruprodukter för SQL-återställning. För mer information besök www.datanumen.com

Kommentarer är stängda.