Empty cell values when another cell value changes in Table environment

Shiro26

Board Regular
Joined
Oct 2, 2015
Messages
82
Hi

I have a Named Table "T_1" that follows the pattern below:

123456789
2
3Header1
Header2
Header3
Header4
4Val1-ato empty when
val1-a
changes
to empty when
val1-a
changes
to empty when
val 1-a
changes
5Val2-aval2-a1to empty when
val2-b and/or
val2-b1
change(s)
to empty when
val 2-b and/or
val2-b1
change(s)
6Val3-aval3-a1val3-c2to empty when
val3-a and/or
val3-b and/or
val3-c
change(s)
7etcetcetcetc
8
9

<tbody>
</tbody>

What I am trying to do is to write the vba code that can delete the value of (turn empty) the cells in columns n+1, n+2 and n+3 (at most) if the value the active cell n is deleted.
The Table above is presenting the three cases that could occure in this example.

However, the Table, as it is, has 4 columns only and I might need to add columns and expand it.

I am trying, at this point to make it work with just the case of Val1-a but I am struggling...

Last piece of information: each of these cells are dynamic drop down lists which are linked to the adjacent left cell value, hence this code to avoid wrong inputs... :)

Can somebody help me figure out what is not right in the code and maybe help me to generalize it?

Thank you so much in advance.

Best Regards,

Shiro


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Cells(ActiveCell.Row, 7)) Is Nothing Then
        Range(Cells(ActiveCell.Row, ActiveCell.Column + 1), Cells(ActiveCell.Row, ActiveCell.Column + 3)).ClearContents
    End If
End Sub
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Watch MrExcel Video

Forum statistics

Threads
1,109,337
Messages
5,528,113
Members
409,802
Latest member
joeino

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top