Search code examples
excelvba

Select columns of selected cells in VBA

I'm trying to select and delete a number of columns in Excel depending if they have certain values in the first row.

Code below. I pulled the function FindAll from http://www.cpearson.com/excel/findall.aspx.

Sub delete_unwanted_columns()

    Dim rng2 As Range
    Set rng2 = FindAll(Range("1:1"), "p-value", xlValues, xlPart)
    Dim rng3 As Range
    Set rng3 = FindAll(Range("1:1"), "type", xlValues, xlPart)

    With ActiveSheet
        Set Rng = Union(rng2, rng3)
    End With

    Rng.Select
'    ActiveSheet.Range(Selection, Selection).EntireColumn.Select
'    Selection.Delete Shift:=Left

End Sub

This gives me this: enter image description here

When I uncomment the second to last line I get this: enter image description here

I would like it to select all the columns that start with "p-value" or "type", but it only selects the first 2. Why would this be and how can I fix?

Solution

  • I have a function that does something similar using a Do While loop to check each cell until it hits a blank cell in my header row. Adapted for you, it looks like this:

    Dim currentCol as Integer
With ActiveWorkbook.ActiveSheet
    Do While Not Cells(1, i) = ""
        currentCol = Cells(1, i).Column
        findValue1 = "type"
        findValue2 = "p-value"

        If InStr(1, Left$(Cells(1, i), 4), findValue1) or InStr(1, Left$(Cells(1, i), 7), findValue2) ' Notice the "4" and the "7" used to limit how much of the cell's value to check... don't want it finding "type" or "p-value" anywhere but at the beginning
            Columns(currentCol).Delete
        Else
            i = i + 1 ' You only need to advance one column if you've deleted a column.
        End IF
    Loop
End With