В начале функции допишите:
Dim lastrow as Long
lastrow = Cells(Rows.Count,"A").End(xlUp).Row
И измените
Range("A1:A1000").Cells
На
Range("A1:A" & lastrow).Cells
Будет работать, если столбец А не содержит пустых ячеек.
Много других способов определения последней строки таблицы:
https://techarks.ru/qa/vba/kak-poluchit-kolichestv...