Fórmula do Excel, Obter última linha em que o intervalo é dinâmico

Question

Eu quero obter o último número de linha de um intervalo, mas o intervalo será dinâmico.

Não consigo usar um intervalo nomeado, pois às vezes o intervalo definido pode mudar na minha planilha. Ou seja, um dia o intervalo nomeado pode ser de 50 linhas, o próximo pode ser de 51. Pelo que sei, o intervalo de nomes não se modifica.

Eu preciso de algo como: Consiga a última linha onde A: A = 'stack'.

Tentando fazer algo como link , com problemas para implementar.

Qualquer ajuda seria apreciada.

    

Answer 1

Em uma coluna com texto, você pode encontrar a última linha que possui um valor específico com essa fórmula. É uma fórmula de matriz e tem que ser confirmada com Ctrl + Shift + Enter

=MAX((A1:A100="stack")*(ROW(A1:A100)))

Veja a célula C1 na imagem abaixo.

Você pode trabalhar essa função em um nome de intervalo dinâmico. Essa fórmula sempre inicia o intervalo em A1 e desce até a última ocorrência de uma célula que possui o texto "pilha":

=OFFSET(Sheet1!$A$1,0,0,MAX((Sheet1!$A$1:$A$100="stack")*(ROW(Sheet1!$A$1:$A$100))),1)

No Gerenciador de nomes, não é necessário usar Ctrl + Shift + Enter, pois todas as fórmulas nomeadas são avaliadas automaticamente como fórmulas de matriz. Se você usar essa abordagem, seria melhor limitar o número de linhas na fórmula e não usar referências de colunas inteiras.

Observe as formigas marchando na captura de tela que circulam o intervalo atual.

Sevocêquiserincluirtodasaslinhasatéaúltimalinhacomtexto,useumafórmulamaissimples.

=OFFSET(Sheet1!$A$1,0,0,MATCH("zzzzz",Sheet1!$A:$A,1),1)

Com o Match, você pode usar uma referência de coluna inteira.

    

Answer 2

Digamos que sua primeira célula nunca muda e digamos que é "A1" . Para isso você deve usar um trecho assim:

private function get_last_row(byref ws as worksheet, optional byval letter_of_first_col as string = "A", optional byval index_of_first_row as interger = 1) as long
    with ws
        get_last_row = .range(letter_of_first_row & index_of_first_row).currentRegion.rows.count + (index_of_first_row - 1)
    end with
end function

Espero que isso ajude você