Getting the name of columns

Question

I want to create a list of column names and match it with the columns existing in the sheets. If the column name does not match up with the list of columns, I have to delete it. So far from what I've researched, they're only giving one column name only. I've been stuck up with this part. Option Explicit

Sub Sample()
Dim strSearch As String
Dim aCell As Range

strSearch = "Salary" 'I want to have a list of column names here

Set aCell = Sheet1.Rows(1).Find(What:=strSearch, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

'if the column name does not match, then the entire column should be deleted

End Sub

Any help?

Put search terms into an Array, then loop thru it. But from what you need to do, finding the unwanted column, you are on wrong approach. Try put "keep" columns in Array1, then loop thru values in row 1 from rightmost going leftwards, if not in Array1, Delete column — PatricK, Apr 29 '16 at 06:43

score 3 · Accepted Answer · edited May 23 '17 at 11:58

Here, I am assuming that your Column Names are in Row 1 of each column.

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

Sub Sample()
    Dim strSearch As Variant
    Dim aCell As Range
    Dim ColumnName As String
    Dim lastcolumn As Long

    strSearch = Array("Salary", "Column1", "Column2") '--->write column names here

    lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column

    For i = lastcolumn To 1 Step -1
        ColumnName = Cells(1, i).Value
        ' check whther column name exists in your array strSearch
        If Not IsInArray(ColumnName, strSearch) Then
            Columns(i).EntireColumn.Delete
        End If
    Next i
End Sub

EDIT: for all worksheets in worbook ______________________________________________________________________________

Sub Sample()
    Dim strSearch As Variant
    Dim aCell As Range
    Dim ColumnName As String
    Dim lastcolumn As Long
    Dim wsh As Worksheet

    strSearch = Array("Salary", "Column1", "Column2") '--->write column names here

    For Each wsh In ActiveWorkbook.Worksheets
        lastcolumn = wsh.Cells(1, Columns.Count).End(xlToLeft).Column
        For i = lastcolumn To 1 Step -1
            ColumnName = wsh.Cells(1, i).Value
            If Not IsInArray(ColumnName, strSearch) Then
                wsh.Columns(i).EntireColumn.Delete
            End If
        Next i
    Next
End Sub

The function IsInArray is wriiten by JimmyPena

Thanks! I will try this one after what I'm doing! This is great! — , Apr 29 '16 at 07:06
These already works. And I have added a code that allows me to jump to the next worksheet and work also the same. But my problem now is I don't know how to stop if it is the last sheet in the workbook. Do you know something about this? Thanks! — , Apr 29 '16 at 08:06
@ramedju - If you are working on all the worksheets of the workbook try this: Dim wsh As Worksheet For Each wsh In ActiveWorkbook.Worksheets '--->your code here Next — Mrig, Apr 29 '16 at 08:12
Thanks for the help, but it's ok now. I've just added an if condition :) — , Apr 29 '16 at 08:29

score 0 · Answer 2 · answered Apr 29 '16 at 06:44

0

Use this to parse through an array of strings in vba:

Dim strSearch As Variant
strSearch = Array("Cat", "Dog", "Rabbit")

For Each element In strSearch
    'do something with element

Next element

answered Apr 29 '16 at 06:44

Ani Menon

27,209
16
105
126

score 0 · Answer 3 · answered Apr 29 '16 at 07:27

Try this.

NOTE: Remember when deleting from a list, you always starts at the end of the list and work back. This way the referencing to the next Column is always correct.

 Sub DeleteUnwantedColumns()

      Dim CurrentWorkSheet As Worksheet
      Set CurrentWorkSheet = ThisWorkbook.Sheets("Sheet1")

      Dim LastColumn As Long
      LastColumn = CurrentWorkSheet.Cells(1, Columns.Count).End(xlToLeft).Column

      Dim KeepColumnArray As Variant
      'Just remember that an Array starts a 0
               'Array Position   0              1
      KeepColumnArray = Array("Salary", "Employee Number")

      Dim KeepColumnString As String
      KeepColumnString = "Salary Employee Number"

      Dim CurrentColumn As Long
      Dim ArrayPosition As Long
      Dim CurrentColumnText As String
      Dim DeleteColumnStatus As Boolean

      For CurrentColumn = LastColumn To 1 Step -1

           CurrentColumnText = CurrentWorkSheet.Cells(1, CurrentColumn).Text

           'Both of the following options work
           'Using InStr is less complicated and is NOT Case Sensitive
           'Using Array is a little complicated and IS Case Sensitive
           'Delete the one you dont want to use

           '=======================================================================================
           'Using an Array
           'Using 'UBound will count the size of the array which will help to prevent you having to
           '  change the loop should the KeepColumnArray change
           For ArrayPosition = 0 To UBound(KeepColumnArray, 1)

                'Setting a "Delete Status" while running through the KeepColumnArray
                If CurrentColumnText = KeepColumnArray(ArrayPosition) Then
                     DeleteColumnStatus = False
                     Exit For
                Else
                     DeleteColumnStatus = True
                End If

           Next ArrayPosition

           If DeleteColumnStatus = True Then CurrentWorkSheet.Columns(CurrentColumn).EntireColumn.Delete
           '=======================================================================================

           '=======================================================================================
           'Using a String
           If InStr(1, KeepColumnString, CurrentColumnText) = 0 Then
                CurrentWorkSheet.Columns(CurrentColumn).EntireColumn.Delete
           End If

      Next CurrentColumn

 End Sub

Getting the name of columns

3 Answers3