# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  > [SOLVED] Message box to confirm user wants to delete record.

## Nitefox

I have created a simple row delete macro to delete the selected row with the following code:



```
Please Login or Register  to view this content.
```


How can i make it so that before deleting the code it comes up with a message box saying 'Are you sure you want to delete record number (then display the data in cell A on the same row as the active cell)'.

----------


## HaHoBe

Hi, James,




```
Please Login or Register  to view this content.
```


Ciao,
Holger

----------


## Nitefox

Thanks Holger, that did the trick.

However when I delete rows in the middle of my records it messes up my record count number in the A column. This is the formula its using:
=IFERROR(IF($D7="", " ", $A6+1), "")

However when I delete a record all the A cells below stop displaying the record number, because the immediate A cell below changes its formula to:
=IFERROR(IF($D7="", " ",#REF!+ 1), "")

----------


## HaHoBe

Hi, James,

do you remember that I asked about pasting the values to the rows when the process is done? Maybe this is the reason why I asked.  :Roll Eyes (Sarcastic): 

You would either need to rewrite the formulas, alter them to use Indirect or use a UserDefinedFunction to relate to the cell above no matter if any row is deleted.

If you can work around by hiding the row instead of deleting it you would not need the other actions but just use



```
Please Login or Register  to view this content.
```


Ciao,
Holger

----------


## Nitefox

I need to delete them, just hiding it doesn't do the trick. How would I rewrite the formula so it doesn't have this issue? This is the current formula im using to work out the record number:
=IFERROR(IF($D7="", " ", $A6+1), "")

----------


## HaHoBe

Hi, James,

for A7 please try


```
Please Login or Register  to view this content.
```


and drag that formula fdown which would match up the numbers without errors if that is what you are after.

Ciao,
Holger

----------


## Nitefox

That fixes the issue when deleting rows, however it causes another issue on the Search page. If you go down to row 317 on Search it starts introducing rows with 0's in each cell. I have had this problem a few times in the past, its seems the VLOOKUP formulas on that page are very picky and I dont know why. The only record number generator I could find on the DataReturn page for Column A that didnt have that knock on effect on the Search page was the previous one I was using.
Workbook Attached.

----------


## HaHoBe

Hi, James,

the question you asked has been answered, hasn´t it? You should make up your mind and rework the design of the whole workbook - not me as a follow up to a VBA question I answered.

Formula for Sheet Search in C293:


```
Please Login or Register  to view this content.
```


Ciao,
Holger

----------


## Nitefox

Updated spreadsheet. See PM. Thanks.

----------


## HaHoBe

Hi, james,

first of all I would ask you to keep the question open to everybody and not limit it to a PM.

Any change with a VBA code will have an influence on the formulas you have in the worksheet, and I remember to have mentioned to cut the number of the formulas down to a small number like ten in advance (not more than a thousand as are in Sheet Search as well as more than 600 in sheet ReurnData - I´m talking about those not having any content but being kept for the future). Each change with a code may lead to altering the formulas which may fit for that purpose but may get messed up with the next question/problem again.

Formula for Sheet Search range C311:


```
Please Login or Register  to view this content.
```


Ciao,
Holger

----------


## Nitefox

That new formula seems to be doing some strange things. I have attached a worksheet with some search criteria already entered. The correct results appear in rows 11 to 22, but then rows 23 to 320 show records that all say n/a. then rows 321 and on appear to show the same record duplicated over and over again.
This is the formula I entered:
C11:
=IF(VLOOKUP(INDIRECT("ReturnData!A" & ROW()-5),ReturnData!$B$6:$K$49940, *3*, FALSE)=0,"",VLOOKUP(INDIRECT("ReturnData!A" & ROW()-5),ReturnData!$B$6:$K$49940, *3*, FALSE))
D11:
=IF(VLOOKUP(INDIRECT("ReturnData!A" & ROW()-5),ReturnData!$B$6:$K$49940, *4*, FALSE)=0,"",VLOOKUP(INDIRECT("ReturnData!A" & ROW()-5),ReturnData!$B$6:$K$49940, *4*, FALSE))
E11:
=IF(VLOOKUP(INDIRECT("ReturnData!A" & ROW()-5),ReturnData!$B$6:$K$49940, *5*, FALSE)=0,"",VLOOKUP(INDIRECT("ReturnData!A" & ROW()-5),ReturnData!$B$6:$K$49940, *5*, FALSE))
etc
(which I then coped down)

----------


## Nitefox

Any thoughts on this Holger? I dont understand why the vlookups on the Search sheet get screwed up due to the record number fromula in A.

----------


## HaHoBe

Hi, James,

ReturnData!$B$6:$K$*49940* could be one key to your question.

Ciao,
Holger

----------


## Nitefox

Thats why I dont understand how the formula in column A affects it, since the vlookup just references column B to K?

----------


## Nitefox

Holger,

I just realised that when the rows get deleted on the ReturnData sheet the vlookup formula on the Search sheet changes to REF:

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

This is whats causing the blank records where the deleted records used to be. How can I stop this from happening? (I think this will probably fix all my issues).

Thanks,
James

----------


## HaHoBe

continued: http://www.excelforum.com/excel-gene...okup-code.html

----------


## Nitefox

Thank you for all your help and time, I appreciate you trying to find a solution for me.

----------

