Time to Execute Code when Deleting Rows

Hi Richard,

The source data whilst on several sheets does all conform to the normalised 2 dimensional format consisting of column label headers and rows that contain individual unique records but it would be both impractical and vitrually impossible to combine it all onto a single sheet without hundreds of hours of manual intervention (which would be self defeating) which could lead to lots of potential errors. Each data source consists of hundreds of thousands of rows of data and the data is updated daily. If you can imagine several different finance systems all outputting different information in different formats, this is what I am dealing with.

I don't have an issue with the different data sources, this is perfectly normal in my experience and pulling the data into the template is not the problem at all and doesn't impact in any way and that process works very effectively and quickly and I'm very happy with the way that works.


I've attached a dramatically simplified sample with three tabs. (I can't upload my main workbook owing to the sensitivity of the data). The 'Data' tab contains the source data (which on my main file would come from the several different sheets), the 'Summary' Tab contains the details for which new worksheets can be created. If column B says 'Complete' then the code will create a new worksheet for each and populate it with data from the 'Data' tab, based on the code in cell C8.

If you run the macro called 'Create Sheets' you will see it creates 3 new worksheets with populated data (once run the codes on the 'Data' tab which previously said 'Complete' now say 'Finished'), however in column E on all three new sheets there are zero values and it is these rows I would like to delete when the worksheets are created.

If you then close and re-open the workbook without saving it and this time run the macro called 'Create Sheets and Delete Rows' you will see it does the same thing, but this time it deletes the rows on the new worksheets containing a zero value.

This is exactly what I'm wanting to achieve only on a much larger scale and with varying ranges, as per the previous sample, but the code that is deleting the rows with a zero value whilst it works when there are only a small number of rows to analyse it takes hours when analysing thousands of rows, so clearly needs a different approach as you pointed out.

If deleting rows is not practical a possible alternative would be to 'hide' the rows with a zero value, but that would be a fallback position.

Although much simplified, the basic functionality is the same and hopefully this helps to demonstrate what I'm trying to achieve a little more clearly.

I hope that helps.

Many thanks