vba - 在 Excel/VBA 中从选择中排除列

Question

我想选择电子表格中的所有列，除了我通过列名称指定的列(列第一行中的值)。列名称将作为参数传递到 Sub 中。例如:

Sub selectAllExcept(columns)
With ActiveSheet
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
Range(Columns(1), Columns(LastCol)).EntireColumn.Select
End Sub

但是，我以某种方式希望能够指定我想要所有列(从第一个到最后一个)，不包括列参数指定的列，我将其设想为逗号分隔的字符串:

columns = "ColumnName1, ColumnName3"

如果列参数包含实际上不是列名称的字符串，代码不会中断，那就太好了。

Answer 1

Sub SelectAllExcept(ByVal except_those As String)
  Dim cn() As String
  cn = Split(except_those, ",")

  Dim i As Long, j As Long
  For i = LBound(cn) To UBound(cn)
    cn(i) = Trim$(cn(i))
  Next


  Dim r As Range

  For i = 1 To ActiveSheet.UsedRange.Columns.Count
    If Not is_in_array(cn, ActiveSheet.Cells(1, i).Value) Then
      If r Is Nothing Then
        Set r = ActiveSheet.Columns(i)
      Else
        Set r = Application.Union(r, ActiveSheet.Columns(i))
      End If
    End If
  Next

  If Not r Is Nothing Then
    r.Select
  End If
End Sub

Private Function is_in_array(arr() As String, val As String) As Boolean
  Dim i As Long

  For i = LBound(arr) To UBound(arr)
    If StrComp(arr(i), val, vbTextCompare) = 0 Then
      is_in_array = True
      Exit Function
    End If
  Next
End Function