I want to delete a column if a cell within a range (in this case the very first row) contains a specific value. I thought I could do it like this:

Public Sub Delete_Column()

Select Case Range(A1:A10)

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

    End Select
End Sub

But it's not working. I'm sure it's the Select Case Range(A1:A10) line that's wrong, but I don't know how to fix it.

CodePudding user response：

Delete Columns (Using 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

CodePudding user response：

Range("A1:A10") is not a first row, it's the column A for the first row. Perhaps you mean Range("A1:J1") ?

Try the modified code below:

Public Sub Delete_Column()

Dim Rng As Range, C As Range

' set the Range object
Set Rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:A10") ' <-- replace "Sheet1" with your sheet's name

' first row should be:
Set Rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:J1") ' <-- replace "Sheet1" with your sheet's name


' loop over each cell in your Range
For Each C In Rng.Cells
    Select Case C.Value
        Case "Birthday", "Gender"
            C.EntireColumn.Delete

    End Select

Next C

End Sub

CodePudding user response：

This should do what you're looking for. Note use of the Range.Find method:

Public Sub Delete_Column()
Dim rng As Excel.Range, searchValue As Variant
    Set rng = Range("A1:A10")
    searchValue = "q"
    If Not rng.Find(searchValue, LookIn:=xlValues) Is Nothing Then
        Debug.Print "found"
    Else
        Debug.Print "not found"
    End If
End Sub