Excel Crash On Delete Row Command

Tosagua
Tosagua used Ask the Experts™
on
I had to finish a macro at home on a machine with an earlier version of MS Exel, however I do not know which version. But the macro worked perfectly, time after time. I emailed the finished worlbook to my work address and attempted to run the macro on Excel 2003.

At the end of the macro there is a YES/NO dialog box. If the user picks NO, the macro works perfectly and ends as it should.

If the user picks YES, the macros starts to execute the command to Delete-Rows-Based-On-Cell-Value, but then crashes with no Error Message.

On the attached file, the problem seems to arise on Lines 20 through 25.

Any assistance or guidance that can be provided would be greatly appreciated.

Tosagua
Delete-Rows-Based-on-Cell-Value.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Can you attach the file you are running the macro against that causes the problem.

I dont see anything in the code you posted that would cause that problem...are you sure the delete row is the problem?

you could try row 22 like this

Rows(i).EntireRow.Delete

but i dont think that's the issue.

Author

Commented:
harr22

Attached is a copy of the Test file along with the complete macro.

I tried running the macro step-by-step (F8), and everything worked fine until I hit the Delete-Row-Based-On-Cell-Value function. And at first, even it seemed to be running well.

I hope you can see what I can not.

Thank you,

Tosagua
EE---TEST-DATA---02-01-10.xls

Commented:
It works fine in excel 2007. I thought I had an instance of 2003 to test with but I dont think i do anymore. I'll see if I can find one. in the mean time, try changing to this Cells(i, "A").EntireRow.Delete  instead of Rows(i).Delete and see if it fixes it.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
harr22:

I seem to have found the offending line of code. At the end of the macro is code that delets column T, if it is empty. I deleted the line and moved the "End If" after the last command.

If Application.CountIf(Range([t2], Cells(Rows.Count, "t")), "Add'l Delivery / Line") = 0 Then [t:t].Delete
  Else


With this line gone, the macro works fine (but without any sound). Since in the commands just previous to this point, we deleted the duplicate rows, column T is already empty. Rather than have a command that checks column T and then deletes it; all we really need is a command that just deletes Column T.

Tosagua
Commented:
I saw that line and expected it to error but it ran fine for me so I let it go...I bet it would work if you added the WorksheetFunction member of the application class like this:

If Application.WorksheetFunction.CountIf(Range([t2], Cells(Rows.Count, "t")), "Add'l Delivery / Line") = 0 Then [t:t].Delete

...maybe 2007 makes that member assumption and previous versions don't. I'm not sure, but ive never used a front end excel function in VBA witout naming the worksheetfunction member.

Like you said, since you have already deleted them all it will always be 0 so you could just delete the column

Columns("T:T").Delete

or they way you hav it would work too

[T:T].Delete

Author

Commented:
harr22,

Excel 2003 really seems sensitive. The Application.WorksheetFunction also crashed Excel, however the Columns("t:t").Delete worked.

Thank you for your assistance.

Tosagua

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial