Im Just Wondering if anyone can help on this one.
Ive written a piece of VBA Code that moves rows of data when one of the fields is a specific value into a new worksheet.

How it is working at the moment is that i select the range i look for then
i replace the values i want with blanks
i select the rows that have blank cells in them
and then copy and paste them into a new worksheet
then i replace the values back in.

the code i use is
This works relatively quickly but this needs to run about 4000 times.
DumpSheet is a string Variable passed into the sub routine.

The Whole process takes about 1/2 - 1 hour to run which is deadtime for the user as they can't use their PC while its happening

Any Ideas
David

Don't have a "solution" per se, but a couple of questions.

Why are replacing the values, then pasting them back into the other sheet? I may not understand your needs, but that seems like an inefficient way to copy the data to another sheet. Are you wanting to copy that data to another sheet, then delete that data from the original sheet?

Also, perhaps it is a typo, but in your second line of code, should that be?

Normally if you select something, then do something with the selection you can omit the selection, so instead of:

range(Myrange).specialcells(xlcelltypeblanks).enti rerow.select
selection.copy

Replace it with:
range(Myrange).specialcells(xlcelltypeblanks).enti rerow.copy

Finally, it is not always necessary to select something to do something with the range/object. Selection slows the process.

Thanks for the Reply

Firstly im replaceing the Values of the cells so i can select all the cells of a specific value among a range of values for example find everything that equals A1ARI

A1ARI
A9ARI
C7ARI

through experimentation the replace of the values to blanks then selecting all the blanks is by far the quickest way i have found of copying accross the data.

The data set tends to be about 50000 rows long and quite a mess
The values get moved onto a seprate sheet so a lot more processing can get done on the removed rows i.e. counting unique values under certain conditions, find new instances of various factors and clearing out redundant data. I found that the processes i put in place to do this took far too long on the original data set and th operators PC would not be usuable for a long time.

sorry that is a typo.

finally ive just had an idea how things could be improved if i cut the data out of the sheet then sort i could get rid of the lengthy deletes as long as nothing dodgy goes on. I need to clear the dataset of all used rows to find new instances of anything that has appeared so new records can be updated within other systems(Sounds like verbal diarrhoea i know)
This has been a work in progress for almost a year now with continual updates.

Any other suggestions would be most welcome ill try to see if i can ditch any of the select statements to see if the speed picks up

Thanks
David

Hi David et al,

Before I start to discuss the perfomance-issue I wonder if You have had or will have problems with the SpecialCells-method.

By design it is limit to 8192 non-contiguous cells.

There exist several approaches that may decrease the time for running it but it is subject how the data is stored in the worksheet.

Is it possible for You to upload an example - Just to have a better understanding.

Thansk in advance,
Dennis