Thread: Import Data Refresh, Row number changes, Easy Fix??

Hi All,

I am having an annoying problem down here in Aus, with an excel suite I have created. The essence of this is based around a query that imports data from a database and formats this on a worksheet so that notes can be made against each entry in adjacent cells.

This is all working fine, the problem comes in when I want to refresh the data and there are new entries imported from the database. Any new rows shift all of the following rows down on the worksheet without moving the cells with the notes in them.

I hope someone knows a simple solution for this, or can point me in the right direction to create a macro that can help to correct this during the refresh process.

If you would like more information please ask.

Thanks in advance fro all your help.

Anyone out there have any ideas on this?

In case anyone is looking for an answer to this, here's how to fix the problem (at least, in Excel 2007):

If you go to the first cell in the returned data, and on the Ribbon, click Data, and then select Properties

Change the radio option for "If the number of rows in the data range changes upon refresh:" to "Overwrite existing cells with new data, clear unused cells", then click OK

The default seems to be "Insert cells for new data, delete unused cells", hence any formulas will be pushed down due to new cells being inserted.