excel - 如何在 case 语句中定义范围？

Question

如果范围内的单元格(在本例中为第一行)包含特定值，我想删除一列。我以为我可以这样做:

Public Sub Delete_Column()

Select Case Range(A1:A10)

Case "Birthday", "Gender"
        cell.EntireColumn.Delete

    End Select
End Sub

但它不起作用。我确定这是错误的 Select Case Range(A1:A10) 行，但我不知道如何修复它。

Answer 1

删除列(使用Union)

Sub DeleteColumns()

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim drg As Range
    Dim cell As Range
    
    ' First 10 cells (columns) in the first worksheet row.
    For Each cell In ws.Range("A1").Resize(, 10).Cells
    ' Specified single-row range in the first worksheet row.
    'For Each cell In ws.Range("A1:J1").Cells
        Select Case CStr(cell.Value)
            Case "Birthday", "Gender"
                If drg Is Nothing Then
                    Set drg = cell
                Else
                    Set drg = Union(drg, cell)
                End If
            'Case Else ' do nothing
        End Select
    Next cell
    
    If drg Is Nothing Then Exit Sub ' no cell found
    
    drg.EntireColumn.Delete
    
End Sub