固定の範囲に対して動くマクロは、だれかが行を追加した最初のときに壊れます。会社のブックではデータが毎週増えるので、マクロは実行するたびにデータの終わりを見つける必要があります。
よく使う方法は、列の一番下から始めて最初の入力のあるセルまで上に移動することで、シートの最後の行からCtrl+Upを押すのと同じです:
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Rows.Countはシートの行数、Cells(Rows.Count, 1)はA列の最後のセル、.End(xlUp).Rowは移動が止まった行番号です。IDのように、どの記録でも埋まっている列を使ってください。一番下から来れば、データの途中の空白は問題になりませんが、End(xlDown)はそこで早く止まってしまいます。最初の空の行はlastRow + 1で、データに対するループは見出しを飛ばすようFor r = 2 To lastRowで回します。
この練習用のブックは1,000行なので、End(xlUp)は1,000行目から始まります。Excelは1,048,576行目から始まり、同じ場所に着きます。