Delete Range by Cells

**magness** · 02-22-2010, 04:00 PM

I'm probably gunna have to slap myself, but why doesn't this work?

Please Login or Register  to view this content.

I threw this in:

Please Login or Register  to view this content.

And it accurately tells me which values of X need deleting. So I assumed that there is something wrong with the statement:

Please Login or Register  to view this content.

However, when I threw the original in it's own sub it worked just fine.

**Leith Ross** · 02-22-2010, 04:12 PM

Hello magness,

When deleting cells or rows, you should start at the bottom and go up. Like this...

Please Login or Register  to view this content.

**magness** · 02-22-2010, 04:28 PM

That gets rid of the need for "X = X - 1", however it still does not execute correctly as the end of the procedure.

Interestingly, I had moved this its own sub in my testing module and did a call which worked. Prepared to just live with that I tried to move the sub into the same location as the original (on a sheet) and now get an error:

Run-time error '1004':
Application-defined or object-defined error

**Leith Ross** · 02-22-2010, 04:40 PM

Hello magness,

In the interest of time, post your workbook so I can review it and find the problem.

**magness** · 02-22-2010, 04:54 PM

It is a big mess, but if you Find "CleanUp" on the Sets sheet that is where the issue is.

Thanks

Count Request Wizard v1.45.xls

**magness** · 02-22-2010, 04:55 PM

I'm about to be out for the day so I won't get back here until tomorrow. Thank you for the help.

**shg** · 02-22-2010, 05:00 PM

Changing the loop control variable inside a loop is a cardinal sin.

Please Login or Register  to view this content.

**Leith Ross** · 02-23-2010, 01:06 PM

Hello magness,

I made a few changes to the macro. The main problem was with Cells. When Cells does not have a qualifier, it refers to the cells on the active sheet. Here is an example.

Please Login or Register  to view this content.

You defined the variable "x" as an Integer. An integer will only allow you to access 32767 rows. To be able to access all rows on a worksheet, even a 2007 worksheet, "x" should be defined as Long. Here is the updated macro.

Please Login or Register  to view this content.

**magness** · 02-24-2010, 11:21 AM

Changing the loop control variable inside a loop is a cardinal sin.

Agree, that was pure idiocy on my part for a number of reasons.

The main problem was with Cells.

*headdesk*

Yep, that would do it. Thank you.

You defined the variable "x" as an Integer.

Good point. I don't expect X to ever get that large, but it couldn't hurt to be covered.

Thank you for taking a look at my mess. Posting the workbook felt a bit like showing folks my dirty room, but perhaps it will inspire me to clean up a bit.