If value in a cell = 0, then delete dependent cells. Check across Row

[mystic_muffin]

Hey, Friends;

I'm having a bunch of trouble with this...

In row 2, I have values either 1/0. I'm trying to come up with some VB that would delete the data in rows 4-6 in the column that that 0 is present in.

So if b2=0, then b4-6 will be deleted. If z2=0 then z4-6 will be deleted. If c2=1, then c4-6 isn't touched.

The code would have to look down row 2 and find the 0's then delete the data under it.

I'm still new to VB so any explanation would be much appreciated!

 

[mystic_muffin]

So this is what I have so far and it works for one column... but i cant get it to work down the full row. I feel like it's something easy that I'm not getting here

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim colindex As Integer
  colindex = 1
 If Cells(2, colindex) = "0" Then Range(Cells(4, colindex), Cells(8, colindex)).Clear
  
End Sub

 

[Jonmo1]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim colindex As Long, lc As Long
If Target.Row <> 2 Then Exit Sub
Application.EnableEvents = False
lc = Cells(2, Columns.Count).End(xlToLeft).Column
On Error GoTo hndlr
For colindex = 1 To lc
    If Cells(2, colindex) = "0" Then Range(Cells(4, colindex), Cells(8, colindex)).Clear
Next colindex
hndlr:
Application.EnableEvents = True
End Sub

 

[mystic_muffin]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim colindex As Long, lc As Long
If Target.Row <> 2 Then Exit Sub
Application.EnableEvents = False
lc = Cells(2, Columns.Count).End(xlToLeft).Column
On Error GoTo hndlr
For colindex = 1 To lc
    If Cells(2, colindex) = "0" Then Range(Cells(4, colindex), Cells(8, colindex)).Clear
Next colindex
hndlr:
Application.EnableEvents = True
End Sub

Thank you so much! This is way more eloquent than anything I could come up with haha

I really appreciate the help!

 

[Rick Rothstein]

Here is a macro that you can try...

[table="width: 500"]
[tr]
	[td]Sub DeleteColumnsWhenRow2Has0InIt()
  With Range("A2").Resize(, Cells(2, Columns.Count).End(xlToLeft).Column)
    .Replace 0, "", xlWhole
    .Cells.SpecialCells(xlBlanks).EntireColumn.Delete
  End With
End Sub[/td]
[/tr]
[/table]