CODEWITHSUNDEEP
vbaexcelspreadsheet
mike
mike

Reputation: 23811

Exclude Columns from Selection in Excel/VBA

I want to select all columns in a spreadsheet except for those which I specify by the column name (value in first row of the column. The column names will be passed as an argument going into a Sub. For example:

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

However, I somehow want to be able to specify that I want all columns (first to last) excluding the ones specified by the columns argument, which I envision as a comma separated string:

columns = "ColumnName1, ColumnName3"

It would also be good if the code did not break if columns argument contained a string which is not actually a column name.

Upvotes: 2

Views: 5459

Answers (1)

GSerg
GSerg

Reputation: 78155

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

Upvotes: 4

Related Questions