Row delete macro is effecting record count formula and vlookup code.

I have created some VB code for deleting a row (See attached Equipment Return Log, on ReturnData sheet. Clicking Delete Row will remove the row containing an active cell). However when a row gets deleted it effects my A column formula which list the record number with the following formula:
=IFERROR(IF($D7="", " ", $A6+1), "")

When the row is deleted, the A cell formula in the row below changes to:
=IFERROR(IF($D7="", " ",#REF!+ 1), "")
and no longer displays the record number for any rows below.

I tried changing this formula to the following code (attachment Equipment Return Log2):
=IFERROR(IF($D7="","",INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1),"")

While this fixes the issue of the record number not working when the records are deleted it prevents some vlookup code from working on the Search sheet. If you go down to row 334, (which contains the last record from the ReturnData sheet) it starts adding blank records. When using the delete record macro the A column on ReturnData sheet now appears to correctly list all the record numbers below, however on the Search sheet a blank row gets left behind where that record used to be, and the Record number on that page stops working from that row on.

Im not sure if the issue is with the record number forumla on the A column, the vlookup formula on the Search page, or both. And I dont see how the Column A formula on ReturnData! is effecting the vlookup formula on Search! since the vlookup code only references columns D to K.

Thank you for your time,
James

Not sure if this helps or not but if you want to auto number put the following into A7 and copy down

=OFFSET(INDIRECT(CELL("address",A7)),-1,0,1,1)+1

Mrice thank you for your comment. It seems to have a similar effect as the second formula I tried did:
=IFERROR(IF($D7="","",INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1),"")

It fixes the issue of the record number not working when the rows get deleted, but it prevents the vlookup code on the Search sheet from working correctly. The vlookup search sill shows a blank row (where the deleted row would have appeared had it not have been deleted). It also doesnt stop at the last record, it continues to number each row all the way down (I would like it to only show a record number if a record is present).

I just noticed that the formula on the search page is getting effected when the records get deleted on the ReturnData page, which seems to be whats causing this issue of creating a blank record. The lookup_value cell gets changed to REF:

=IFERROR(VLOOKUP(ReturnData!#REF!,ReturnData!$B$6:$K$49972, 3, FALSE),"")