Please Help Improve the Efficiency of this Macro

Hello Everyone!

I've had a handful of posts the past few days (hopefully you're not all sick of me yet!).

So this is what is going on: I have a macro to delete all rows above a specific value. In this case, a person pastes an online generated report into a specific sheet in a dashboard.

The macro works great (and I even managed to get the two buttons on the sheet to resize and be placed where I want so they aren't eventually deleted with the rows being deleted), but the time it takes to run the macro is still far too long.

The reason I am trying to include this macro is to future proof the dashboard against changes in the online report's layout. In the past month the report changed columns and rows slightly such that the original references no longer worked for the dashboard.

This is my attempt at future-proofing (and it works, just too slowly).

Please Login or Register  to view this content.

Any assistance or alternative avenues would be greatly appreciated!

Hi Richard,

So I experimented with deleting the Hlookups from the full workbook and they are clearly one of the main resource drains (hands down).

Is there a suitable alternative you are aware of that is more efficient to replace them? I tried this:

Please Login or Register  to view this content.

But any time the macro to delete the extra rows is run, it destroys the "Match" reference and I get a #Ref Error. The Hlookups did not create errors even when rows were deleted/changed.

But I have an idea that you might be able to help with: If I have the macro that deletes the extra rows also copy/paste the relevant columns where I want them, that might solve my issue entirely of too many formulas slowing down the workbook.

So if you look at the columns on the DataParse tab, those are the headers I care about. There are extra ones pasted from the online generated report that do not matter.

Is there a way to have the macro do the following:

1. Delete the extra rows
2. Copy all the remaining data in the columns I care about to the DataParse tab (in the order I have them on that tab currently- Some columns will go from Col A on one sheet to Col A on another, but others won't be direct). Essentially a sort of Hlookup in the macro.
3. Still reform the buttons as I have them on the datapaste tab.
4. Not copy the buttons to the data parse tab
5. Make everyone Happier

Hello Everyone,

I believe I have come to a suitable solution. My final code is below. It's still slower than I'd like, but significantly faster (and more functional) than it ever was previously.

Please Login or Register  to view this content.