No Excel 2010, como posso criar um modelo com uma função que termina automaticamente na última célula com dados?

Question

Eu quero criar um modelo no Excel com uma função que inclua um intervalo, mas o número de células nesse intervalo será alterado toda vez que o modelo for aplicado.

Como posso identificar que quero que o intervalo comece em, digamos, B2 e termine na última célula com texto na coluna B?

    

Answer 1

Isso depende do que você está fazendo e, como tal, ajudaria se você adicionasse um pouco mais de detalhes à sua pergunta. Como é, não podemos dizer o que você está tentando fazer ou por quê.

Como tal, só posso aconselhá-lo sobre como criar intervalos automáticos (dinâmicos):

1

Use uma tabela - as tabelas do Excel são incrivelmente poderosas e contam automaticamente como um intervalo, se você quiser se referir a cada célula de uma coluna, basta digitar [ nome da coluna ] em vez da referência de célula tradicional. As tabelas do Excel são expandidas automaticamente à medida que novas linhas são adicionadas também, o que é bastante útil.

O lado negativo das tabelas nos traz de volta à questão de o que é esse modelo e o que ele deve fazer: se o tamplate deve ser acessado por usuários do Excel 2003 ou tabelas anteriores não funcionará.

2

O que me leva à opção 2: intervalos nomeados dinâmicos.

• Selecione a coluna que você deseja definir com um intervalo automático clicando na primeira célula de dados (geralmente linha 2) e vá para Fórmulas > Definir nome > Definir nome


• Dê um nome claro ao nome, como ColumnNameRange


• deixe o Scope as Workbook e adicione um comentário se você quiser


• Em Refere-se a: insira esta fórmula (substituindo as referências de célula e folha conforme necessário)

=OFFSET(SheetName!$A$2,0,0,COUNTA(SheetName!$A$2:$A$500),)

Este intervalo nomeado Dyanmic, quando mencionado em uma fórmula, retornará tudo do intervalo indicado no deslocamento para a segunda metade do intervalo indicado no COUNTA. A fórmula analisa todo o intervalo no COUNTA para obter o número de células não em branco e, em seguida, cria uma seleção de intervalo de deslocamento da primeira célula, até o número de células encontradas.

Mais informações sobre intervalos nomeados dinâmicos aqui: link
Mais informações sobre as Tabelas aqui: link

Algumas coisas para ter em mente RE Intervalos nomeados dinâmicos:

• Se você definir essa segunda parte do COUNTA para 500 como está acima e terminar com mais de 500 linhas de dados, ela não contará os extras, por isso lembre-se


• Se houver espaços em branco nos dados que o COUNTA está examinando, ele será contado até o primeiro espaço em branco. Para evitar isso, defina sempre o COUNTA para a primeira coluna (que deve ser sua coluna id, portanto, nunca deve ter espaços em branco) e defina a célula referenciada no OFFSET para a coluna desejada (portanto, para a coluna D defina o COUNTA como $ A $ 2 : $ A $ 500 e o DESLOCAMENTO para $ D $ 2


• Sempre corrija todos os intervalos de células com $ (F4) ou isso não funcionará corretamente. Todos os intervalos de células devem se parecer com esta coluna $ $ row


• Você pode criar um Intervalo Nomeado Dinâmico que abrange uma coleção de colunas adicionando outro COUNTA após o primeiro: = OFFSET ($ A $ 1,0,0, COUNTA ($ A1: $ A500), COUNTA ($ B1: $ B500)). Você pode fazer isso com tabelas declarando algumas colunas: = Tabela1 [[Coluna1]: [Coluna2]] ou declarando a tabela inteira: = Tabela1

Answer 2

Aqui está uma macro que fará a seleção

Public Sub SelectRange()

Cells(2, 2).Select
Range(Selection, Selection.End(xlDown)).Select

*do stuff*

End Sub

Agora basta substituir fazer as coisas com o que você quer fazer com a seleção e gerar os resultados para qualquer célula em que você colocaria a fórmula.

Se você nos disser a fórmula, podemos adicioná-la na vba. Se você precisar de entrada do usuário antes de a fórmula entrar em vigor, você poderá adicioná-la a um botão ou a outra ação.