deleting rows screws formulas

[wombat323]

I've written a macro which formats a table in Worksheet 1. Worksheet 2 is full of formulas that reads from Worksheet 1. But when I add a 'remove duplicate rows' process to the macro, it leaves me with rows in Worksheet 2 that are full of errors.

Maybe to explain better... My workflow is:

1.Copy data (from external table) into Worksheet1
2. Run Macro - "FormatTable".
3. Run Macro "Remove Duplicate Rows"**
4. Check Worksheet 2

The duplicate rows that have been deleted from Worksheet 1 are still in Worksheet 2, but with #REF in each cell. Everything is fine in Worksheet2 after step2. It's step3 that ruins it!

How do I get the 'delete duplicate rows' thing to work on Worksheet2.

** I got this from http://www.cpearson.com/excel/duplicat.htm

cheers!

[MrShorty]

When you delete the rows in worksheet1, Excel tries to update the formulas that referred to the deleted rows, but it has no idea what to refer to, since those rows were simply deleted. What would you like to do? Delete the rows that referred to the deleted rows? Change the formulas so they refer to something else (and what would you like to change them to)? Hide the rows with errors? Something else?

[Bryan Hessey]

I've written a macro which formats a table in Worksheet 1. Worksheet 2 is full of formulas that reads from Worksheet 1. But when I add a 'remove duplicate rows' process to the macro, it leaves me with rows in Worksheet 2 that are full of errors.

Maybe to explain better... My workflow is:

1.Copy data (from external table) into Worksheet1
2. Run Macro - "FormatTable".
3. Run Macro "Remove Duplicate Rows"**
4. Check Worksheet 2

The duplicate rows that have been deleted from Worksheet 1 are still in Worksheet 2, but with #REF in each cell. Everything is fine in Worksheet2 after step2. It's step3 that ruins it!

How do I get the 'delete duplicate rows' thing to work on Worksheet2.

** I got this from http://www.cpearson.com/excel/duplicat.htm

cheers!

Is there a reason you cannot remove the duplicates before you Format Table?

1.Copy data (from external table) into Worksheet1
3. Run Macro "Remove Duplicate Rows"**
2. Run Macro - "FormatTable".
4. Check Worksheet 2

Or, alternately, can you, in worksheet2, Copy the data, and Paste Special = Values back over itsself

---