Macro to get values and consolidate them.

Difficult to comment without seeing the request in context, but you may find that a better solution is to filter the data and do the copying and deleting en bloc.

Whenever you have this many rows try and avoid loops like the plague. In fact try and avoid loops full stop if possible. They are always without exception very slow, particularly when each iteration through the loop has to jump backwards and forward between VBA and the Excel App.

Always try and use bog standard Excel functionality since this will always be more efficient. Even if you need to encode it in a macro. In cases like this the fastest way I know is to use a Data Filter to highlight the data and then do with the filtered data whatever you want. If you have many formulae and filtering is slow then first switch to manual calc., switrching back to automatic at the end.

Try the following which is the equivalent of your looping macro

Please Login or Register  to view this content.

Sorry , I forgot to add the workbook (and mention that I've added a working sheet). It's now attached.

The macro is quite simple. It first filters your column C to identify the non blank cells. It then copies these to the 'Working' sheet until the filter is removed, and then they're copied back to the original sheet.

Are you running it against your small sample of data for which it was written? You'll need to adapt it for your actual data.

Have you switched off automatic calculation? Believe me you should not be using a loop to process this data. Data Filter is the way to go, but given the 50000 rows you'll be better advised to switch off calculation since filtering otherwise causes recalculations to take place.

@AB33

I don't think you're breaking the rules since this is a continuation of the subject of the thread not a new question.

Yes, as far as I'm aware and having used Data Filtering for more years than I care to remember, invoking Data Filtering from VBA is just as if you run it directly from Excel. Which is pretty much what you'd expect.

I have seen mention of Tables (introduced in 2007 onwards) sometimes not filtering correctly. At this site http://forums.techguy.org/business-a...-problems.html this very same problem has been discussed. If you look at post #11 there is an attachment which does demonstrate an anomaly. Filtering any of the columns always includes the last row 94 of the table. A suggested 'solution' in post #12 is to convert the table to a range. When I tried that though it made no difference. However when I copied the table and pasted it to a blank sheet as a range it filtered correctly.

Not sure where that leaves us since it does seem there is a bug. Perhaps someone else (an MVP perhaps) will chip in and comment. I tend not to use tables in preference to a normal range mostly because formula referencing is not as intuitive, and unless you have a particular need to use the gee whiz formatting stuff it may be better to avoid them.

As a general rule if I have hundreds of rows which need manipulating with a filter or updating with formulae, and I know it's not necessary for calculations to take place during the macro, (e.g. when any formula that are added are not going to be converted to values before the macro finishes), I generally switch off automatic calculation until the macro has finished.