Uma macro que trabalha com um intervalo fixo quebra na primeira vez que alguém adiciona uma linha. Em uma pasta de trabalho de empresa, os dados crescem toda semana, então a macro precisa descobrir onde os dados terminam cada vez que é executada.
O jeito mais comum é começar no fim de uma coluna e subir até a primeira célula preenchida, o mesmo que pressionar Ctrl+Seta para cima a partir da última linha da planilha:
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Rows.Count é o número de linhas de uma planilha, Cells(Rows.Count, 1) é a última célula da coluna A e .End(xlUp).Row é o número da linha onde o salto para. Use uma coluna que todos os registros preencham, como um ID. Uma célula em branco no meio dos dados não importa quando você vem de baixo, mas faz End(xlDown) parar cedo demais. A primeira linha vazia é lastRow + 1, e um loop pelos dados usa For r = 2 To lastRow para pular o cabeçalho.
Esta pasta de trabalho de prática tem 1.000 linhas, então End(xlUp) começa na linha 1.000; o Excel começa na linha 1.048.576 e chega ao mesmo lugar.
O loop sempre chega ao último pedido, por mais linhas que a planilha tenha hoje.