Solved: Deleting Columns on Conditions Matched with an Array

[Slicemahn]

Hi Everyone,

A second pair of eyes will hopefully clear this problem up. Is there something wrong with my code I get an errow when I step through to the Tester line. Here's the code:

[VBA]Sub DeleteColumnsonCondition()
Dim EndRow As Integer
Dim EndColumn As Integer
Dim ColumnCounter As Integer
Dim CellCompare As String
Dim Tester As Variant
Dim MyArray As Variant
' Find the end points of the data
' Set an array as a list to compare against cells
MyArray = Array("Calls", "TFN", "Area Code", "Volume")
EndColumn = Cells(1, 256).End(xlToLeft).Column
EndRow = Cells(65536, 2).End(xlUp).Row
Tester = 0
For ColumnCounter = EndColumn To 1 Step -1
CellCompare = Cells(1, ColumnCounter).Value
Tester = Application.WorksheetFunction.Match(CellCompare, MyArray, 0)
MsgBox Tester
' The next lines of codes will check if the match function will yield a number
' if the result of tester is indeed a number then we now take that value in the cell checked in our list (MyArray)
' conversely if it is not then the columns are to be deleted
If IsNumeric(Tester) = False Then
Columns(ColumnCounter).Delete
End If
Tester = Empty
Next ColumnCounter
End Sub[/VBA]

Is there something wrong with my code? Please let me know. Many thanks for your assistance.

Slicemahn

[Bob Phillips]

[vba]

Sub DeleteColumnsonCondition()
Dim EndRow As Integer
Dim EndColumn As Integer
Dim ColumnCounter As Integer
Dim CellCompare As String
Dim Tester As Variant
Dim MyArray As Variant
' Find the end points of the data
' Set an array as a list to compare against cells
MyArray = Array("Calls", "TFN", "Area Code", "Volume")
EndColumn = Cells(1, 256).End(xlToLeft).Column
EndRow = Cells(65536, 2).End(xlUp).Row
For ColumnCounter = EndColumn To 1 Step -1
CellCompare = Cells(1, ColumnCounter).Value
Tester = 0
On Error Resume Next
Tester = Application.WorksheetFunction.Match(CellCompare, MyArray, 0)
On Error GoTo 0
If Tester = 0 Then
Columns(ColumnCounter).Delete
End If
Tester = Empty
Next ColumnCounter
End Sub
[/vba]

[Slicemahn]

You see what a second pair of eyes can do! Xld thanks alot from saving my eyes from the burning!