VBA Delete column where first cell is a string of array

Question

I have an excel file in which I would like delete columns that aren't useful.

Say I have 4 columns: 'ID', 'Name', 'Surname', 'Job'. What I'm trying to do is delete all columns that aren't 'ID' and 'Job'.

I tried using the Range.Find method, but there's obviously something I'm getting wrong.

Any help appreciated

myArray = Array("ID", "Job")
'Delete columns which title aren't in myArray
For j = mycol To 1 Step -1 'col by col
    For k = 0 To 4 'for each element in myArray
        ' Delete col where no elements of myArray are present.
        Columns(j).Delete
    Next k
Next j

EDIT: By columns names, I mean the first row of each column. So A1=ID, B1=Name etc.

what do you mean by column name? is it just a header in cell in certain row or you are using Tables (ListObjects)? — Kazimierz Jawor
– Kazimierz Jawor, Commented Nov 14, 2016 at 13:03
@KazimierzJawor By columns names, I mean the first row of each column. So A1=ID, B1=Name etc. — DanielMc
– DanielMc, Commented Nov 14, 2016 at 13:11
@Daniel Mc check the code in my answer below, let me know if it that's what you meant in your post — Shai Rado
– Shai Rado, Commented Nov 14, 2016 at 13:16
@ShaiRado That's exactly what I meant. I can't believe I didn't find something so obvious. Thanks — DanielMc
– DanielMc, Commented Nov 14, 2016 at 13:21

Kazimierz Jawor · Accepted Answer · 2016-11-14 13:20:17Z

1

Or you could try with something easier:

For j = myCol To 1 Step -1 'col by col
    'check if column name is in array
    If IsError(Application.Match(Cells(1, j), myArray, 0)) Then
        Columns(j).Delete
    End If

Next j

answered Nov 14, 2016 at 13:20

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

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

2 Comments

DanielMc Over a year ago

That's a very nice method, didn't know it existed!

Shai Rado Over a year ago

@KazimierzJawor it is a nice and cleaner answer

Shai Rado · Accepted Answer · 2016-11-14 13:13:14Z

The code below will loop through all columns (according to your mycol value), and checks if the header in the first row matches any of the elements inside myArray array.

Sub DeleteColumns_NotinArray()

Dim myArray
Dim j As Long, k As Long, mycol As Long
Dim ColHeadrFound   As Boolean

myArray = Array("ID", "Job")

' just for my testing
mycol = 10

'Delete columns which title aren't in myArray
For j = mycol To 1 Step -1 'col by col

    ' reset flag
    ColHeadrFound = False
    For k = 0 To UBound(myArray) 'for each element in myArray
        If Cells(1, j).Value = myArray(k) Then
            ColHeadrFound = True
            Exit For
        End If
    Next k
    ' Delete column where no elements of myArray are present
    If ColHeadrFound = False Then
        Columns(j).EntireColumn.Delete
    End If

Next j


End Sub

Collectives™ on Stack Overflow

VBA Delete column where first cell is a string of array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related