Excel VBA - Delete columns if column name <> "string name"

Question

I have data that I frequently export from an source workbook (format is consistent, i.e., all column names are located in the 1st row). I only need the data from about 8 or so columns spaced sporadically throughout the worksheet ("Sheet1"). Below is the code I have so far to do this.

I am having two problems,

the macro is deleting all of my data, and
My If statement is very inefficient. I would like to create a variable that includes all the strings containing the column names I wish to not delete. I'm thinking creating an array, but I am not sure if my loop would act correctly
```
i.e., colNames = array("Col 1 that I want to keep", "Col 2", etc)
```

The Code:

Sub test2()

Dim currentSht As Worksheet
Dim i As Integer
Dim lastRow As Long, lastCol As Long
Dim startCell As Range

Set currentSht = ActiveWorkbook.Sheets("Sheet1")
Set startCell = currentSht.Range("A1")

lastRow = startCell.SpecialCells(xlCellTypeLastCell).Row
lastCol = startCell.SpecialCells(xlCellTypeLastCell).Column

With currentSht
    For i = lastCol To 1 Step -1
        If .Cells(1, i).Text <> "First Name" Or .Cells(1, i).Text <> "Last Name" Or .Cells(1, i).Text <> "OB Hours(hr)" Or .Cells(1, i).Text <> "OB Talk Time (TT)" Or .Cells(1, i).Text <> "OB ACW Time" Or .Cells(1, i).Text <> "Handled-OB" Or .Cells(1, i).Text <> "Handled-OB/hr" Or .Cells(1, i).Text <> "TT Avg" Or .Cells(1, i).Text <> "Max Talk Time" Then
        Columns(i).EntireColumn.Delete
    End If
Next i
End With

End Sub

Thanks again everyone.

Change the Or to And in the if statement

Scott Craner
– Scott Craner

2016-06-06 18:33:13 +00:00
Commented Jun 6, 2016 at 18:33 — Scott Craner
– Scott Craner, Commented Jun 6, 2016 at 18:33

Scott Craner · Accepted Answer · 2016-06-06 18:39:32Z

2

To your first question:

Change the Or to And in the if statement

To your second:

Sub test2()

Dim currentSht As Worksheet
Dim i As Long, j As Long
Dim lastRow As Long, lastCol As Long
Dim startCell As Range
Dim colnames
Dim here As Boolean

colnames = Array("First Name", "Last Name", "OB Hours(hr)", "OB Talk Time (TT)", "OB ACW Time", "Handled-OB", "Handled-OB/hr", "TT Avg", "Max Talk Time")

Set currentSht = ActiveWorkbook.Sheets("Sheet1")
Set startCell = currentSht.Range("A1")

lastRow = startCell.SpecialCells(xlCellTypeLastCell).Row
lastCol = startCell.SpecialCells(xlCellTypeLastCell).Column

With currentSht
    For i = lastCol To 1 Step -1
        here = False
        For j = LBound(colnames) To UBound(colnames)
            If .Cells(1, i).Value = colnames(j) Then
                here = True
                Exit For
            End If
        Next j
        If Not here Then
            Columns(i).EntireColumn.Delete
        End If        
    Next i
End With

End Sub

answered Jun 6, 2016 at 18:39

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andy Over a year ago

This looks great, thanks. I will try it out shortly...also, this just dawned on me. I am used to writing similar loops for counting rows. For that, I use the Step -1. Does this need to be adjusted to something like Step (0,-1) since we are dealing with Columns now?

Scott Craner Over a year ago

@AndyG nope, the columns are integers like the rows.

Ne Mo Over a year ago

I'm from the future, but this rocks. A tip I encountered while implementing it: if you're deleting loads and loads of columns, Excel will throw an error because it can't accept lines that are too long. I'm not sure what the exact limit is. In that case, inside the array bracket, you need to separate it into several lines by putting after the comma a space then an underscore and then a new line. So it looks like: "name10", "name11", _line break "name12", name13" and so on

Collectives™ on Stack Overflow

Excel VBA - Delete columns if column name <> "string name"

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related