时间戳

首页 > 解决方案 > 清除Excel工作表中多行的依赖下拉列表

excel - 清除Excel工作表中多行的依赖下拉列表

问题描述

我有 excel 表。当我将国家/地区值从美国更改为加拿大时，该字段具有国家和州字段，应清除州字段。我已经使用下面的代码对单行进行了此操作，但是我无法对具有相同格式的多行执行此操作，请您指导我做同样的事情吗

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$3" Then
Cells(3, 26).Value = "Please select"
End If

End Sub

截屏

标签： excelvbadrop-down-menu

解决方案

Please try the code below instead of what you have.

Private Sub Worksheet_Change(ByVal Target As Range)

    Const FirstDataRow As Long = 2          ' modify as appropriate
    Const CtryClm As String = "E"           ' modify as appropriate
    Const StateClm As String = "D"          ' modify as appropriate

    Dim Rng As Range
    Dim Msg As String

    Set Rng = Range(Cells(FirstDataRow, CtryClm), _
                    Cells(Rows.Count, CtryClm).End(xlUp))
    With Target
        If .Cells.CountLarge = 1 Then
            If Not Application.Intersect(Target, Rng) Is Nothing Then
                If Len(.Value) Then Msg = "Please select"
                Application.EnableEvents = False
                Cells(.Row, StateClm).Value = Msg
                Application.EnableEvents = True
            End If
        End If
    End With
End Sub

The code will respond to a change in any single cell (not when you paste multiple cells at once) and enter "Please select" only if the entry in the CountryColumn isn't "".

推荐阅读