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:
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.
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.
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 aqui..
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
'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
Observação: 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 vínculo de célula da caixa de combinação (D2 reflete a seleção do terceiro elemento no intervalo, que começa em A2, juntamente com uma função ÍNDICE em “E3” para encontrar o diretor (=ÍNDICE(B:B;D2+1;1)).
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 reparo rar e produtos de software de recuperação SQL. Para mais informações visite www.datanumen.com