writing a loop to only read drop down cells in 2 columns

[vze]

First I am a novice to writing these macros in excel and hence I am struggling to fix a run time error that occurs.
My ask is as follows

I have worksheet with multiple columns - some are text cells and some are data drop down lists.
Two of the columns are dependent drop down lists which have some validation being performed.
Column 9 is a root cause column.
Column 10 is sub root cause.

1. When I select a value in Column 9 it automatically displays a drop down list of values in column 10 based on the selection made in column 9
2. If I change the value of column 9 the list in column 10 also updates

Currently the following works as desired.

1. I select column 9 value and the correct drop down list displays in column 10 and is selectable with no errors
2. I change the value in column 9 ad the list in column 10 changes accordingly to the correct drop down list with no errors
3. If both cols 9 & 10 are populated and I select column 9 cell and hit delete, then column 10 is reset to a blank with no errors

The code below works and achieves what I want BUT if I mass select rows in the 2 columns mentioned above and hit delete, I get a run time error (see screenshot)

• If I select both cols 9 and 10 values on a single row and hit delete the both cells for both columns reset to blank with no errors
• I can select all the rows and columns of my table and hit delete with no errors occurring, but the run time occurs ONLY if I select multiple rows of the 2 columns 9 & 10 highlighted (for example if I wanted to clear all the rows where data exists for these 2 columns but leave the data in all the other cells then this run time occurs. )

I was told to put the statement into a loop but have no clue how to achieve that without breaking my current code or any of the above working points. When I do anything, something else breaks and its due to my limited Knowledge

1. How can I put below code into a simple loop to only validate the 2 columns and permit me to mass select the rows for the 2 columns only and be able to hit delete?
2. I also want to be able to insert rows in between the range of rows existing and still have the loop recognize the rows may have increased from 20 rows to 40 rows (for example).

Can anyone please help me as I am beyond delayed completing a task as I have bee n struggling due to my limited knowledge
Thanks in advance

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
'if column 9 has a value selected from the dropdown then set column 10 to a blank
'if column 9 & 10 have values selected and a new value in column 9 is chosen then set column 10 to a blank (to reset it)
If Target.Column = 9 Then
If Target.Validation.Type = 3 Then
Target.Offset(0, 1).Value = ""
Else
If Target.Value = "" Then
Target.Offset(0, 1).Value = ""
End If
End If
End If
Application.EnableEvents = True
End Sub

 

[BSALV]

i didn't check it, but i guess by only looking at the changes in your 9th column, it'll be okay.

Private Sub Worksheet_Change(ByVal Target As Range)
     'if column 9 has a value selected from the dropdown then set column 10 to a blank
     'if column 9 & 10 have values selected and a new value in column 9 is chosen then set column 10 to a blank (to reset it)

     Set c = Intersect(Target, Columns(9))                      'only the changed cells of the 9th column
     If c Is Nothing Then Exit Sub

     Application.EnableEvents = False
     For Each cl In c.Cells 'loop through all those changed cells
          If cl.Validation.Type = 3 Or cl.Value = "" Then cl.Offset(0, 1).Value = ""
     Next
     Application.EnableEvents = True

End Sub

 

[BSALV]

better solution, with an "on error" in case of no validation in column 9

Private Sub Worksheet_Change(ByVal Target As Range)
     'if column 9 has a value selected from the dropdown then set column 10 to a blank
     'if column 9 & 10 have values selected and a new value in column 9 is chosen then set column 10 to a blank (to reset it)

     Set c = Intersect(Target, Columns(9))                      'only the changed cells of the 9th column
     If c Is Nothing Then Exit Sub

     Application.EnableEvents = False
     For Each cl In c.Cells                                     'loop through all those changed cells
          v = Null                                              'reset before next cell
          On Error Resume Next
          v = cl.Validation.Type                                'check the validation type (causes error if no validation)
          On Error GoTo 0
          If v = 3 Or cl.Value = "" Then cl.Offset(0, 1).Value = ""
     Next
     Application.EnableEvents = True

End Sub