I need to create a reset button that will reset rows containing a specific value

[odin78]

So here is my dilemma, I've created a booking system for my B&B hotel and I need to find a way to delete (reset) all rows with data of my guests that have stayed and checked out of my hotel. It gets extremely frustrating to delete each cell to make room for more bookings as you can imagine. Below is a screenshot of my current excel sheet. I've managed to make a "Reset all stayed bookings" button on the top right of my sheet however I am stuck on the programming of the button in the VBA editor. What I need is to reset all rows containing the word "Hospedado" in column F (It's all in portuguese). However, column H/J/O/R/S/T has code in it and will be reset automatically if the other cells are blank so these must be left alone. I am very confident in excel code however it's my first time venturing into the VBA editor so help would be very much appreciated!

Best regards,
Andreas B.

Booking.png

[zico8]

Ok,

Firstly you could show what you have in VBA by now - this is important to see how you built this table (or you used only excel sheet filling in manually?).

This table is excel 'data table' created from insert menu?

Which columns indeed you want to delete data from VBA?

[odin78]

Hi, thanks for the reply. I haven't edited anything in VBA. All coding so far is done outside VBA with "Conditional formatting" and cell formulas. All data on the sheet is filled in manually.

What I need to do is create a reset button that resets all data in rows containing the word "Hospedado" in row F21:F170. However, there are certain columns that have code in them so I don't want the reset button to "harm" those in any way.

For example: Row F50 contains the word "Hospedado" therefore when i click the "Reset" button the cells F50:G50 and I50:N50 and P50:Q50 will be cleared.

I hope I made myself clear and you have a good answer.

Thanks for the help!

Regards,
Andreas B.

[jaslake]

Hi Andreas

Welcome to the Forum!!!

Please attach a sample workbook the Structure of which is the same as your actual workbook.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

INCLUDE ANY CODE YOU'RE USING OR HAVE TRIED.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[odin78]

Because of sensitive data on the sheet and details of my guests I cannot share or show the entire workbook.

I've succesfully created a macro button and managed to code it so it resets the rows containing the word "Hospedado", however I need to make a code for every single row and that's over 150 rows! So that's not going to work.
The code I've used in the VBA is:

Please Login or Register  to view this content.

This works deleting the row F21 however I want it to search the entire column F for the word "Hospedado" and clear the entire row corresponding.

I've tried this without success

Please Login or Register  to view this content.

Basically, to explain as best I can... this is the situation before:
Before.png

And when everything is working I want it to look like this AFTER I press the "Reset" button:
After.png

The cells are coded so they some are blue and some are red when blank.

I hope this clears things up.

Thanks,
Andreas B.

[odin78]

I really need help with this guys... let me explain in a better way...

This code does exactly what I want:

Please Login or Register  to view this content.

That's only for the first row F. I have another 150 rows that need the same coding. So this works as well:

Please Login or Register  to view this content.

And so on... if I continue with this code 150 times down I have solved my problem, however, there must be a simpler way to do it? I've tried this and it does NOT work...

Please Login or Register  to view this content.

Any ideas?

Thanks and regards,
Andreas B.

[jaslake]

Hi Andreas

See if this works for you...

Please Login or Register  to view this content.

[odin78]

Thank you, this works almost and clears all "Hospedado" cells in column F, however it only clears the cells in column F and not the rows corresponding to the cells cleared

Getting closer anyways

[jaslake]

Hi Andreas

Sorry, misunderstood...

Please Login or Register  to view this content.

[odin78]

Jaslake, thank you so much, it works great! May I ask one more question, is there a way to skip Column H only, because it clears the code in the cells also and does not work anymore after I reset. The cells in Column H clear automatically if cells in Column G are blank.

[jaslake]

Not tested but should work...

Please Login or Register  to view this content.

[odin78]

Nevermind Jaslake, I think I cracked it with this code...

Please Login or Register  to view this content.

Thanks again!

[shg]

Please take a few minutes to read the forum rules, and then edit your post to add CODE tags.

Thanks.

[jaslake]

You're looping through the Cells twice...which is ok but not necessary.

[jaslake]

Sorry about that shg...completely missed that...

[odin78]

Sorry Shg, I've edited my posts. New on the forum

[shg]

No problem, welcome, and thank you.

[odin78]

Jaslake, your last code works perfectly! Thank you!

[jaslake]

You're welcome...glad I could help.

If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Thanks for adding Code Tags.