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.
Currently the following works as desired.
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)
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
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.
- 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
- If I change the value of column 9 the list in column 10 also updates
Currently the following works as desired.
- I select column 9 value and the correct drop down list displays in column 10 and is selectable with no errors
- I change the value in column 9 ad the list in column 10 changes accordingly to the correct drop down list with no errors
- 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. )
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