Como ajustar automaticamente a caixa de combinação ou lista com base em intervalos de dados dinâmicos no Excel

Compartilhe agora:

Quando os dados baixados de um banco de dados excedem o intervalo de uma caixa de combinação, os novos itens simplesmente não são exibidos. Para combater isso, o intervalo subjacente às caixas de lista ou combinação precisa ser expandido ou contraído para corresponder aos dados. Este artigo examina como fazer isso automaticamente. 

Supõe-se que o leitor tenha a faixa Desenvolvedor exibida e esteja familiarizado com o Editor VBA. Se não, por favor, Google “Excel Developer Tab” ou “Excel Code Window”.

Uma maneira profissional de exibir caixas de combinação é expandir ou contrair seus intervalos conforme necessário. Por exemplo:Uma maneira profissional de exibir caixas de combinação

A chave para um intervalo dinâmico é monitorar o número de linhas preenchidas na coluna relevante, usando a função =countA. Esta função conta os elementos populados em uma sequência de células até atingir a última; no caso do primeiro diagrama da imagem acima, seria a linha 11.

Manter um intervalo requer automaticamente nomes definidos para rastrear o número de linhas preenchidas. Por exemplo, usamos eCol (coluna final) e eRow (linha final) para definir os limites do nosso intervalo. Quanto mais linhas preenchermos, maior será o valor de eRow.Use eCol e eRow para definir os limites de alcance

Os nomes definidos acima definiram a lista para uma coluna de largura (eCol = 1) pelo número de linhas preenchidas em eCol (eRow = 11)

Finalmente, os Títulos aparecem no intervalo “A2:A” & eRow. Você notará que a função Index é usada para estabelecer a última célula no intervalo chamado “Títulos”. Efetivamente, o intervalo “A2:A” e eRow se traduz em “A2:A11” neste estágio.Função de índice

Podemos configurar o intervalo dinâmico automaticamente quando a pasta de trabalho é aberta, usando o subprocedimento Auto_open, que é executado antes que a pasta de trabalho se torne visível.

O código

Abra uma pasta de trabalho e preencha-a com uma caixa de combinação e alguns dados. A pasta de trabalho de amostra usada neste exercício pode ser encontrada SUA PARTICIPAÇÃO FAZ A DIFERENÇA.

Abra a janela de código VBA e insira um módulo. Copie o código abaixo no módulo.

O evento Auto_Open configura os valores da última linha e da última coluna para o intervalo dinâmico “Títulos” e observa as alterações subsequentes feitas.

Sub auto_open() Dim eRow As Integer, eCol As Integer, i As Long On Error Resume Next 'Limpa os nomes de definição atuais, para evitar duplicações activeworkbook.Names("eCol").Delete activeworkbook.Names("eRow"). Delete activeworkbook.Names("Titles").Delete Range("A1").Selecione 'Títulos aparecerão na primeira coluna, A neste caso eCol = 1 'Encontre a última linha preenchida eRow = Sheets("Main"). Cells(Rows.Count, eCol).End(xlUp).Row 'Define os nomes 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 "Direcionado por " & Cells(2, 5) End Sub

Nota: Tudo foi colocado em uma única página para simplificar a visualização. Normalmente, os valores nas colunas A e B e D e E estariam em outra planilha, possivelmente oculta. Observe também que a segunda coluna, B, não é usada nas definições do intervalo neste exercício específico; os valores da coluna B são obtidos da maneira usual por meio da propriedade de link da célula da caixa de combinação (D2 reflete a seleção do terceiro elemento no intervalo, que étarts em A2, juntamente com uma função Index em “E3” para encontrar o diretor ( =INDEX(B:B,D2+1,1)).A caixa de combinação será preenchida inicialmente por Auto_open

Salve a pasta de trabalho e abra-a novamente. A caixa de combinação será preenchida inicialmente por Auto_open. Adicione itens às colunas A e B e observe as alterações na caixa de combinação.

Recuperar Arquivos Excel Danificados

De vez em quando, os arquivos do Excel podem ser corrompidos após o Excel travar inesperadamente. Se você tiver um backup, poderá simplesmente restaurar os dados com seu backup. Caso contrário, pode ser necessário procurar um profissional especializado ou ferramenta para recuperar o Excel corrompido arquivos.

Introdução do autor:

Felix Hooker é um especialista em recuperação de dados em DataNumen, Inc., líder mundial em tecnologias de recuperação de dados, incluindo rar reparar e produtos de software de recuperação SQL. Para mais informações visite www.datanumen.com

Compartilhe agora:

Uma resposta para “Como ajustar automaticamente a caixa de combinação ou lista com base em intervalos de dados dinâmicos no Excel”

Deixe um comentário

O seu endereço de e-mail não será publicado. Os campos obrigatórios são marcados com *