speedup the code for deleting rows

[maheshwow]

Hi,
I have this code which is supposed to delete rows containing "0" and "null" in all worksheets.
But this is taking too much time and seems like system is gonna hang.

Is there anything we can do to speed up code. Also, its "search all worksheet" function is not working, and is doing work in just the active worksheet.
Please help.

Please Login or Register  to view this content.

[Kelshaer]

Use this:

Please Login or Register  to view this content.

[BigBas]

If you can avoid using a loop, it may generally be faster. Without your workbook, I can only guess if this would work, but try code such as the following:

Please Login or Register  to view this content.

Let me know if this works.

[maheshwow]

Thankyou for immediate response,

I've put both codes in the attached sample workbook,
Mr.Kelshaer's code worked well, but was not applying to whole workbook. It is running only in active sheet.
Mr.BigBas code worked on all sheets, but deleted all rows. i couldn't understand "A1" & "A2" used in this code, hence could not modify it.

SAMPLE.xls

I'll working on workbook similar to attached one, please help me me with updated code.

[Kelshaer]

Sorry, I used you code in my previous post.
Please try this:

Please Login or Register  to view this content.

[BigBas]

Based on your workbook structure, I'd go with Kelshaer's method. Whenever possible, you want to avoid using multiple loops in workbooks when built-in excel commands work, but in your case, with the data setup as it is, it's more difficult to use the filter/delete method. Fortunately, the workbook as posted is not so large as to be resource intensive.

[maheshwow]

Thanks again, but i'm getting "object does not support this property or method" error. Snapshot & updated excel sample attached.
sample2.jpg

SAMPLE2.xls

please take look at attachments..

[Kelshaer]

I don't know why it is giving you an error while it works with me.
any way I've changed the code a little bit. please try this and let me know

Please Login or Register  to view this content.

[BigBas]

In mahesh's sample, the following line is incorrect:

Please Login or Register  to view this content.

Mahesh, you only included

Please Login or Register  to view this content.

You must include the .Worksheets

[maheshwow]

Thankyou very much. This one worked.. even though its giving strange row height in next to last row. But i'm not concerned abt formatting.
I really appreciate your help.!!


Now i know i've taken too much of your time, but If you can spare little more, please look at my post for "merging worksheet with forced repetition" http://www.excelforum.com/excel-prog...99#post2684299. Could be of tremendous help for me.

This has remained unanswered even after multiple requests..... pls just let me know it its not possible with vba.