Clear range if conditional cell is blank

[JJM0512]

Hi

I am trying to find a way of automatically clearing the contents of columns C:AT in row x if the user manually clears the contents in column B in row x.

I have found a way of doing it for the first row in my sheet (row 3), however I would like to replicate this for the succeeding 100 rows. The following VBA code works for row 3...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("B3")) Is Nothing Then
        Range("C3:AT3").ClearContents
    End If
End Sub

I must also point out that this code succeeds the follwing VBA code...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim p As Range

    For Each p In Range("A1:At1").Cells
        If p.Value = "N" Then
            p.EntireColumn.Hidden = True
        ElseIf p.Value = "Y" Then
            p.EntireColumn.Hidden = False

        End If
    Next p
End Sub

So it would need to be compatible with this one too.

Hope someone can help!

Thanks
Joe

 

[Fluff]

Will they be clearing cells individually, or could they be clearing multiple cells at once?

 

[DanteAmor]

Hi and welcome to MrExcel

Change all your code for the following;

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, rng2 As Range, c As Range
  
  Set rng = Intersect(Target, Range("B3:B" & Rows.Count))
  If Not rng Is Nothing Then
    For Each c In rng
      If c.Value = "" Then
        Application.EnableEvents = False
        Range("C" & c.Row & ":AT" & c.Row).ClearContents
        Application.EnableEvents = True
      End If
    Next
  End If
  
  Set rng2 = Intersect(Target, Rows(1))
  If Not rng2 Is Nothing Then
    For Each c In rng2
      If c.Value = "N" Then
        c.EntireColumn.Hidden = True
      ElseIf c.Value = "Y" Then
        c.EntireColumn.Hidden = False
      End If
    Next
  End If
End Sub

 

[JJM0512]

Sorry, obviously wasn't clear then (note to self)

Best to use an example i think...

If B3 was to be cleared by the user so that it were blank, I would want only C3:AT3 to be cleared automatically.
If B4 was to be cleared by the user so that it were blank, I would want only C4:AT4 to be cleared automatically.

Will they be clearing cells individually, or could they be clearing multiple cells at once?

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim p As Range
   
   For Each p In Range("A1:At1").Cells
      p.EntireColumn.Hidden = p.Value = "N"
   Next p
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 2 And Target = "" Then
      Application.EnableEvents = False
      Target.Offset(, 1).Resize(, 44).Value = ""
   End If
   Application.EnableEvents = True
End Sub

 

[JJM0512]

Hi and welcome to MrExcel

Change all your code for the following;

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, rng2 As Range, c As Range
 
  Set rng = Intersect(Target, Range("B3:B" & Rows.Count))
  If Not rng Is Nothing Then
    For Each c In rng
      If c.Value = "" Then
        Application.EnableEvents = False
        Range("C" & c.Row & ":AT" & c.Row).ClearContents
        Application.EnableEvents = True
      End If
    Next
  End If
 
  Set rng2 = Intersect(Target, Rows(1))
  If Not rng2 Is Nothing Then
    For Each c In rng2
      If c.Value = "N" Then
        c.EntireColumn.Hidden = True
      ElseIf c.Value = "Y" Then
        c.EntireColumn.Hidden = False
      End If
    Next
  End If
End Sub

Hi there

Thanks for the quick reply.

Now when I clear the text in column C, columns C:AT have now cleared! However unfortunately, the columns with an "N" in row 1 now don't hide

Any ideas to resolve this?

Kind regards
Joe

 

[JJM0512]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim p As Range
  
   For Each p In Range("A1:At1").Cells
      p.EntireColumn.Hidden = p.Value = "N"
   Next p
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 2 And Target = "" Then
      Application.EnableEvents = False
      Target.Offset(, 1).Resize(, 44).Value = ""
   End If
   Application.EnableEvents = True
End Sub

I'm so grateful for this but also so sorry, I misunderstood your first question.

The user is able to clear multiple cells in column B all at once.

 

[DanteAmor]

Now when I clear the text in column C, columns C:AT have now cleared

Your requirement is on column B. Try again but deleting data in column B, you can delete multiple cells at the same time.

I am trying to find a way of automatically clearing the contents of columns C:AT in row x if the user manually clears the contents in column B in row x.

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, rng2 As Range, c As Range, p as range
  
  Set rng = Intersect(Target, Range("B3:B" & Rows.Count))
  If Not rng Is Nothing Then
    For Each c In rng
      If c.Value = "" Then
        Application.EnableEvents = False
        Range("C" & c.Row & ":AT" & c.Row).ClearContents
        Application.EnableEvents = True
      End If
    Next
  End If
  
   For Each p In Range("A1:At1").Cells
        If p.Value = "N" Then
            p.EntireColumn.Hidden = True
        ElseIf p.Value = "Y" Then
            p.EntireColumn.Hidden = False
        End If
    Next p
End Sub

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim p As Range, Rng As Range

   For Each p In Range("A1:At1").Cells
      p.EntireColumn.Hidden = p.Value = "N"
   Next p
   If Not Intersect(Target, Range("B:B")) Is Nothing Then
      Application.EnableEvents = False
      For Each p In Intersect(Target, Range("B:B"))
         If p = "" Then p.Offset(, 1).Resize(, 44).Value = ""
      Next p
   End If
   Application.EnableEvents = True
End Sub

 

[JJM0512]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim p As Range, Rng As Range

   For Each p In Range("A1:At1").Cells
      p.EntireColumn.Hidden = p.Value = "N"
   Next p
   If Not Intersect(Target, Range("B:B")) Is Nothing Then
      Application.EnableEvents = False
      For Each p In Intersect(Target, Range("B:B"))
         If p = "" Then p.Offset(, 1).Resize(, 44).Value = ""
      Next p
   End If
   Application.EnableEvents = True
End Sub

Hero