Macro which deletes rows stops at empty row

Hi Guys

I'm pretty clueless when it comes to this, so please bear with me. Basically i have a simple macro which adds/deletes rows of locations in an excel worksheet, one click of the add button adds a row, and one click of the delete button deletes a row. If there is more than one location for a customer, the user clicks add and it inserts a row, and of course vice versa.

I have the row to be copied stored in another worksheet (which I will hide) so that even if all the rows are deleted, I still have the source row, but the problem is that when all the location rows are deleted it starts deleting all the other rows below. The next row after the locations is blank, so is there anything I can insert into the code to stop the macro deleting a row if it is blank?

Thanks
John

Please post a sample workbook.

Regards

Hi there, I've attached the example workbook. As you can see it's very basic. All I want to do is when the user clicks on "Delete Location", when all the locations are deleted I want the macro to stop i.e. when there is a blank row in row 64.

Any help would be greatly appreciated!

John

Is the file okay?

Yes, it's fine ... just been busy.

Try this:

Please Login or Register  to view this content.

Regards

Improved Insert Location module:

Please Login or Register  to view this content.

Regards

Is the solution okay? ;-)

Oh my! Worked like a charm! Thank you SO much, my boss is going to love me now
John

You're very welcome. Thanks for the feedback.

You don't necessarily need the message box, if you don't want it. Just comment it out. Then pressing the button will just appear to have no effect ... but it won't require positive action to dismiss the message.

Regards

Hi again!

I have another problem:

My boss told me they needed another section with the ability to insert and delete rows. So now I have two sections that do this.

It's not a problem for the first section, but if I add or delete rows in the first section it messes up my macros for the second section!

Any ideas? I've attached my file again...this might be an awkward one

OK. I understand.

I have it sussed but it may be tomorrow before I can spend time on the fine detail and testing.

It's not that difficult so it shouldn't take too long once I can focus on it.

Just so you know where we're at.

Regards

Try this.

All subroutines combined into one module. Other modules removed.

Original code (my code, that is) modified to work relative to identifiable section headers, rather than fixed rows and ranges.

New code replicated for subcontractor addition and deletion.

Seems resilient enough ... but not tested to destruction :-)

If your boss wants any more sections adding and deleting, you should be able to work it out from this. Have fun.

Take care

HOLY MACKEREL!!! You really are amazing! Thanks SO much. I'm going to test it thoroughly today, although it seems to work brilliantly from what I've seen...absolute genius.

I'm going to really work hard to completely understand what you've done there as well, it's really clever...

Again, thanks so much!!!!

Hey - just to be extra annoying, there's one more thing which I can't figure out - is it possible to add a checkbox (as shown in the attached file) so that it copies to each row when a location is added?
The purpose is to have a checkbox next to each additional site location for the user to tick if it needs to be visited. If not I guess it would be simpler just to mark an 'x' in a cell?

Thanks
John

"one more thing" ... just, "one more thing"? Hahh.

Give me a while; I'll get back to you.

Regards

OK, see the attached updated workbook.

Let me know how you get on.

If you're getting a bonus for this, just send me a cheque :-)

Regards

Haha - brilliant... thanks again!

What can I say? Thanks for the feedback.