Quicker function than delete entire row

**davidparkes** · 04-12-2011, 03:40 PM

Hi

I have some example data and macro attached that has about 20,000 rows in size the real data maybe upto 100,000 rows.

Is there a quicker way of combinig the data as per the macro using clear contents of the row and resort or some other logical test ?

The delete entire row takes 3mins to run for the 20,000 test rows.

Any help would be good

Thanks
David

**Chinchin** · 04-12-2011, 04:33 PM

Try this:

Please Login or Register  to view this content.

**davesexcel** · 04-12-2011, 08:07 PM

The fastest way to do a loop is not selecting cells, for example this code will copy and paste all the used cells into the other worksheet

Please Login or Register  to view this content.

I am not sure what you are doing with the other part of the code, so I am not going to get into it.
If you attach another sample workbook showing what the desired results are to be, that would be a great benefit, you only need to provide 50 to 100 rows as the code somebody will provide to you will probably be dynamic.

**davidparkes** · 04-18-2011, 08:47 AM

Hi

Basically if B1=B2 and C1=C2, then add the value of F2 to F1 and then delete row 2 and so on.

This take a long time due to the delete row function.

I was wondering if there was a better/quicker way of writing this, i.e.

if B1=B2 and C1=C2, then add F2 to F1 and then clear contents of row 2, then check if B1=B3 and C1=C3 if so add F3 to F1 and so on, but if B1<>B2 or C1<>C2 then move down one row or to the next occupied cell in column B.

Then I can resort which will remove the blank rows.

I hope this makes sense.

Thanks
David

**Chinchin** · 04-18-2011, 09:08 AM

Try this - I've found that unioning then removing all at once is faster than one by one:

Please Login or Register  to view this content.

**Whizbang** · 04-18-2011, 09:12 AM

Try this:

Please Login or Register  to view this content.

**Krishnakumar** · 04-18-2011, 11:06 AM

Hi,

Try

Please Login or Register  to view this content.

HTH

**Marcol** · 04-18-2011, 01:09 PM

Here is another attempt, it doesn't involve looping, it might be quicker this way.

Please Login or Register  to view this content.

The code could possibly be tidied a bit.

Hope this helps

**davidparkes** · 04-18-2011, 02:15 PM

Hi Guys

Thankyou to you all for your help, and solutions.

It's amazing you can have so many different ways to do the same task...

I shall mark this as solved and add to your rep's

Cheers
David