Why is this Do Loop not working right?

evilrtc · 08-15-2007, 04:34 PM

The loop cycles through my sheet correctly but wont delete any cells. What's the deal?

Please Login or Register  to view this content.

**Ron Coderre** · 08-15-2007, 04:52 PM

I'm not sure if you really want to delete cells or just clear the contents.

I'll assume you want to clear the contents:

This code test each cell from E2 through the last non-blank Col_E cell. If the cell's value is TRUE (the text TRUE, not the boolean value), the contents of the corresponding cell in Col_B are cleared.

Please Login or Register  to view this content.

Does that help?
If not....what do you want your code to do?

evilrtc · 08-15-2007, 05:29 PM

That is exactly what I want my code to do. Thank you so much! I'm grateful for you code but was just still curious why mine was not working.

Thanks again!

evilrtc · 08-16-2007, 03:23 PM

Please Login or Register  to view this content.

I tried this variation of your code on a different section of what I'm working on. It only half works though. I use one purge routine to take out the contents of one column, then a second purge routine to take out a second column. If the two purge routines result in both columns being cleared then I have this third purge that just deletes the record entirely.

However, its not deleting every record with two blank columns, even when they show "TRUE" in the G column. I'm not sure how to get it to do that. Thanks for everything by the way!

**royUK** · 08-16-2007, 03:56 PM

Try this

Please Login or Register  to view this content.

**Ron Coderre** · 08-16-2007, 04:09 PM

When deleting rows, you need to start from the BOTTOM....Otherwise, it's like cutting off the tree limb you're standing on.

Try this:

Please Login or Register  to view this content.

Does that help?

evilrtc · 08-16-2007, 04:20 PM

Didn't work unfortunately. Maybe it needs to be step 1 instead of step -1?

Still learning a great deal sorry!

I appreciate everyone's help here though. You guys are awesome!

evilrtc · 08-16-2007, 04:23 PM

RoyUK's was the one I tried I will try the latest. Thanks again!

evilrtc · 08-20-2007, 02:35 PM

My sheet contains 51000 records roughly. How long would this take to cycle all the way through. I let it run for two and a half hours and it was still going when I came back.

How long should this take?

**avveerkar** · 08-20-2007, 03:16 PM

Originally Posted by evilrtc

My sheet contains 51000 records roughly. How long would this take to cycle all the way through. I let it run for two and a half hours and it was still going when I came back.

How long should this take?

A few seconds really. Is your Page break view on? Set it off.

RoyUK's solution should work. What is the problem you are facing? What do you mean not working? The cell must contain only "TRUE" - no leading or lagging spaces. If you may have spaces use TRIM to remove the spaces. Modify one line to If TRIM(UCase(cCell.Value)) = "TRUE" Then
A V Veerkar

**Antti** · 08-21-2007, 01:52 AM

Remember to set:

Please Login or Register  to view this content.

I've seen that normally over 99% of the execution time is spent for screen updates.

Originally Posted by evilrtc

My sheet contains 51000 records roughly. How long would this take to cycle all the way through. I let it run for two and a half hours and it was still going when I came back.

How long should this take?