VBA to clear contents, not formulas of multiple rows based off a value in cell

[efrana]

Was hoping for some help getting a code written that will work for me. I've found some different codes that work, but none specific. I'm basically new to VBA, so I'm really struggling piecing together a good understanding of these examples I'm finding.
Basically, by placing an "x" in any cell of column B, I'd like an automatic code that runs and clears contents(but not formulas) of that entire row, including the column B that was just entered? If an automated code is unattainable, I would be extremely content with a code I could attach to a button to manually run as needed?
I really appreciate any assistance, thanks!
I'm running Excel365. Currently, I have two codes attached to form control buttons that run various "auto sort" type functions in this spreadsheet, these are in the Module 2. I created those by recording my actions. Not sure if you needed to know this?

 

[Michael M]

I doubt if you will get a code to clear cells but leave formulas intact.
I'd suggest rewriting the formulas to incude the criteria of if "B = "x" then......

 

[Peter_SSs]

You could try this Worksheet_Change event code to see if it does what you want. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Test by entering "x" (or anything else - but we can limit it to only x if required) in a cell or multiple cells in column B

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
 
  Set Changed = Intersect(Target, Columns("B"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If Len(c.Value) > 0 Then c.EntireRow.SpecialCells(xlConstants).ClearContents
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[bebo021999]

Place this code in sheet code (Right click on tab name, view code)
Type "x" (or "X", not case sensitive) in column B, then that all non formular cells in entire rows will be cleared

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Column <> 2 Then Exit Sub
If UCase(Target.Value) = "X" And Target.Count = 1 Then
    Set rng = Rows(Target.Row)
    With rng.SpecialCells(xlCellTypeConstants)
        .ClearContents
    End With
End If
End Sub

 

[efrana]

You could try this Worksheet_Change event code to see if it does what you want. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Test by entering "x" (or anything else - but we can limit it to only x if required) in a cell or multiple cells in column B

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
 
  Set Changed = Intersect(Target, Columns("B"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If Len(c.Value) > 0 Then c.EntireRow.SpecialCells(xlConstants).ClearContents
    Next c
    Application.EnableEvents = True
  End If
End Sub

This code did exactly what I needed! Thank you so much One small inquiry to add, something I hadn't considered until I did it. I wanted to protect the sheet, but this code won't run when protected. Is there a bypass or other option somewhere to enable this code to run while the sheet is protected?

 

[Peter_SSs]

Adjust to suit your sheet's protection password

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range

  Set Changed = Intersect(Target, Columns("B"))
  If Not Changed Is Nothing Then
    ActiveSheet.Unprotect Password:="abc"
    Application.EnableEvents = False
    For Each c In Changed
      If Len(c.Value) > 0 Then c.EntireRow.SpecialCells(xlConstants).ClearContents
    Next c
    ActiveSheet.Protect Password:="abc"
    Application.EnableEvents = True
  End If
End Sub

 

[efrana]

Adjust to suit your sheet's protection password

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range

  Set Changed = Intersect(Target, Columns("B"))
  If Not Changed Is Nothing Then
    ActiveSheet.Unprotect Password:="abc"
    Application.EnableEvents = False
    For Each c In Changed
      If Len(c.Value) > 0 Then c.EntireRow.SpecialCells(xlConstants).ClearContents
    Next c
    ActiveSheet.Protect Password:="abc"
    Application.EnableEvents = True
  End If
End Sub

Another spot on fix. Thanks sincerely!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[efrana]

You're welcome. Thanks for the follow-up.

So, I've made some changes over time to this document and have recently wondered if it's possible to do the following. Was really hoping you could assist me again? I've attached an image for visual explanation.

Currently, the code you gave me in this thread is still the exact code I'm running, with the exception of columns ("K") instead of ("B") being the clear entire row trigger. I was hoping for the ability to add a clear range option into the mix. With each of the locations (columns E:J) I would like to have any cell value for that row cleared anytime a cell value is added to one of those columns.
If the product is in WIP with a value in Cell F7, but the next day it been progressed into the Blast process, when I enter a value into Cell H7 that F7 would clear. Column K would still be used to clear the entire row.
Thoughts?

 

[Peter_SSs]

I've attached an image for visual explanation.

We cannot tell from an image what are formulas and what are not. We also cannot easily copy the data for testing. Please consider XL2BB for sample data.

Changing Columns("B") to Columns("K") in the previous code works for me when I enter something in K7, unless the "x" in F7 is the result of a formula. In what way does it not work for you?