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

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

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.

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:

Private Sub CommandButton1_Click()

If Range("F21").Value = "Hospedado" Then
Range("F21", "X21").Value = ""
End If

End Sub

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

Private Sub CommandButton1_Click()

If Range("F21":"F170").Value = "Hospedado" Then
Range("F21":"X170").Value = ""
End If

End Sub

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.

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

This code does exactly what I want:

Private Sub CommandButton1_Click()

If Range("F21").Value = "Hospedado" Then
Range("F21", "X21").Value = ""
End If

End Sub

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

Private Sub CommandButton1_Click()

If Range("F21").Value = "Hospedado" Then
Range("F21", "X21").Value = ""
End If

If Range("F22").Value = "Hospedado" Then
Range("F22", "X22").Value = ""
End If

If Range("F23").Value = "Hospedado" Then
Range("F23", "X23").Value = ""
End If

End Sub

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...

Private Sub CommandButton1_Click()

If Range("F21":"F170").Value = "Hospedado" Then
Range("F21", "X170").Value = ""
End If

End Sub

Any ideas?

Thanks and regards,
Andreas B.

Hi Andreas

See if this works for you...

Option Explicit

Private Sub CommandButton1_Click()
   Dim Rng          As Range
   Dim cel          As Range
   Dim LR           As Long

   With ActiveSheet
      LR = .Range("F" & .Rows.Count).End(xlUp).Row
      Set Rng = .Range("F21:F" & LR)
      For Each cel In Rng
         If cel.Value = "Hospedado" Then
            cel.Value = ""
            .Cells(cel.Row, "X").Value = ""
         End If
      Next cel
   End With
End Sub

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

Hi Andreas

Sorry, misunderstood...

Option Explicit

Private Sub CommandButton1_Click()
   Dim Rng          As Range
   Dim cel          As Range
   Dim LR           As Long

   With ActiveSheet
      LR = .Range("F" & .Rows.Count).End(xlUp).Row
      Set Rng = .Range("F21:F" & LR)
      For Each cel In Rng
         If cel.Value = "Hospedado" Then
            .Range(.Cells(cel.Row, "F"), .Cells(cel.Row, "X")).Value = ""
         End If
      Next cel
   End With
End Sub

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

Private Sub CommandButton1_Click()


   Dim Rng          As Range
   Dim cel          As Range
   Dim LR           As Long

   With ActiveSheet
      LR = .Range("F" & .Rows.Count).End(xlUp).Row
      Set Rng = .Range("F21:F" & LR)
      For Each cel In Rng
         If cel.Value = "Hospedado" Then
            .Range(.Cells(cel.Row, "I"), .Cells(cel.Row, "N")).Value = ""
         End If
      Next cel
   End With
   

   With ActiveSheet
      LR = .Range("F" & .Rows.Count).End(xlUp).Row
      Set Rng = .Range("F21:F" & LR)
      For Each cel In Rng
         If cel.Value = "Hospedado" Then
            .Range(.Cells(cel.Row, "F"), .Cells(cel.Row, "G")).Value = ""
         End If
      Next cel
   End With
   


End Sub

Thanks again!

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

Not tested but should work...

Option Explicit

Private Sub CommandButton1_Click()
   Dim Rng          As Range
   Dim cel          As Range
   Dim LR           As Long

   With ActiveSheet
      LR = .Range("F" & .Rows.Count).End(xlUp).Row
      Set Rng = .Range("F21:F" & LR)
      For Each cel In Rng
         If cel.Value = "Hospedado" Then
            .Range(.Cells(cel.Row, "F"), .Cells(cel.Row, "G")).Value = ""
            .Range(.Cells(cel.Row, "I"), .Cells(cel.Row, "X")).Value = ""
         End If
      Next cel
   End With
End Sub

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

Thanks.

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

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.

No problem, welcome, and thank you.