Help understanding VBA code - Delete unwanted data base on value of first row/cell of column

[lj97]

Sub DeleteRedundantData()

Mylist = Array("Apple", "Bananna", "Pear", "Orange", "Melon")

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

For mycol = LC To 1 Step -1

    x = ""
    On Error Resume Next
    x = WorksheetFunction.Match(Cells(1, mycol), Mylist, 0)
    If Not IsNumeric(x) Then Columns(mycol).EntireColumn.Delete

Next mycol

End Sub

Hi all! I found this code online and it has been extremely useful for me to clear unwanted columns in a dataset based off the value in the first row/cell of a column.


Example below:

The text in the Mylist = Array (i.e. "Apple", Bananna", "Pear", "Orange", "Melon") are the columns to be kept, with these values in the first row of their columns.

If I have a data range with colums whose first row/cell begins with Apple, Bananna, Pear, Orange, Melon, Avocado, Mushroom, Pineapple, Carrot and I deploy the above macro/VBA code, only the columns with first cell values listed in the Mylist = Array will be kept (i.e. Avocado, Mushroom, Pineapple, Carrot columns will be deleted).

Just wondering if someone would be able to break this down for me further and assist me with what is specifically occuring during each line of the code? I understand some of it, but not all.

I have unfortunately neglected to save the website/user I got this code from but will absolutely post a link to it if I can find it!

 

[RobP]

Sub DeleteRedundantData()

'define your array of items to check against.
Mylist = Array("Apple", "Bananna", "Pear", "Orange", "Melon")

'this line finds the last column in Row 1 that contains data on your sheet.  "LC" presumably was used for "Last Column" and will store a column number. ie. if Column "E" is your last column, then LC = 5
LC = Cells(1, Columns.Count).End(xlToLeft).Column


'define a count, starting with your last column, and counting backwards to col.1 (step -1 = count backwards)
For mycol = LC To 1 Step -1

    x = ""   'define x as NULL
    On Error Resume Next  ' this line simply says if you come across an ERROr, ignore it and carry on. Not often much use in VBA code. 

' So this line uses worksheet.match to look at each cell in row 1 (starting lets say cell E5 (Cells(1 (= row) ,mycol (column) = 5 from above example I gave), and compares it with whats in your Array of words (MyList), and the ,"0" on the end says look for an EXACT match.  If it finds a word in cell E5, matching exactly with a word in MyList array, it will give "x" a numerical value representing the number it is in MyList.  eg. if it found "Pear", x would be set at 3 as Pear is 3rd in your list.
    x = WorksheetFunction.Match(Cells(1, mycol), Mylist, 0)

'then as x has been set from NULL to a "numeric value" ie. "3", it is checked to see if it is simply a number or not (if nothing was found, x would still be "" or NULL which is not a number.
if x is NOt a Number, then delete the column number MyCol or "5" = Column "E"
    If Not IsNumeric(x) Then Columns(mycol).EntireColumn.Delete

Next mycol 'then go check whats in column 4.  The reason to count backwards when deleting columns is that the data to the right is effectively shifted left by 1. Meaning if we started with column 1 and deleted col2, col 3 would effectively become col 2 .. and what you are looking for gets quite confused ... eg. it would miss columns when searching.

End Sub

 

[RobP]

Hope my comments in line above help.
cheers
Rob

 

[rlv01]

I might code the same function a bit differently.

Sub DeleteRedundantData2()
    Dim LC As Long, MyCol As Long
    
    LC = Cells(1, Columns.Count).End(xlToLeft).Column                    'Count number of columns with data.
    
    For MyCol = LC To 1 Step -1
        Select Case Cells(1, MyCol).Value
            Case "Apple", "Bananna", "Pear", "Orange", "Melon"           'Columns beginning with these words are retained
            Case Else
                Columns(MyCol).Delete                                    'All other columns are deleted
        End Select
    Next MyCol
End Sub

 

[lj97]

Thanks Rob for going through each line with your comments, greatly appreciated

And thank you rlv01 for your re-work! I will have a sus.

 

[RobP]

Sub DeleteRedundantData()

'define your array of items to check against.
Mylist = Array("Apple", "Bananna", "Pear", "Orange", "Melon")

'this line finds the last column in Row 1 that contains data on your sheet.  "LC" presumably was used for "Last Column" and will store a column number. ie. if Column "E" is your last column, then LC = 5
LC = Cells(1, Columns.Count).End(xlToLeft).Column


'define a count, starting with your last column, and counting backwards to col.1 (step -1 = count backwards)
For mycol = LC To 1 Step -1

    x = ""   'define x as NULL
    On Error Resume Next  ' this line simply says if you come across an ERROr, ignore it and carry on. Not often much use in VBA code.

' So this line uses worksheet.match to look at each cell in row 1 (starting lets say cell E5 (Cells(1 (= row) ,mycol (column) = 5 from above example I gave), and compares it with whats in your Array of words (MyList), and the ,"0" on the end says look for an EXACT match.  If it finds a word in cell E5, matching exactly with a word in MyList array, it will give "x" a numerical value representing the number it is in MyList.  eg. if it found "Pear", x would be set at 3 as Pear is 3rd in your list.
    x = WorksheetFunction.Match(Cells(1, mycol), Mylist, 0)

'then as x has been set from NULL to a "numeric value" ie. "3", it is checked to see if it is simply a number or not (if nothing was found, x would still be "" or NULL which is not a number.
if x is NOt a Number, then delete the column number MyCol or "5" = Column "E"
    If Not IsNumeric(x) Then Columns(mycol).EntireColumn.Delete

Next mycol 'then go check whats in column 4.  The reason to count backwards when deleting columns is that the data to the right is effectively shifted left by 1. Meaning if we started with column 1 and deleted col2, col 3 would effectively become col 2 .. and what you are looking for gets quite confused ... eg. it would miss columns when searching.

End Sub

apologies, when I wrote "E5" above as the example - I actually meant "E1" (E = col 5, 1 = Row 1 of course..)