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

vze

New Member
Joined
Apr 7, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • new code issue.png
    new code issue.png
    140.2 KB · Views: 9

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
i didn't check it, but i guess by only looking at the changes in your 9th column, it'll be okay.
VBA Code:
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
 
Upvote 0
better solution, with an "on error" in case of no validation in column 9
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,967
Messages
6,175,667
Members
452,666
Latest member
AllexDee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top