Hamond
12-29-2011, 12:15 PM
Hi,
I have come up with the macro below to delete columns that do not meet multiple criteria. Basically I want to delete all columns in a given range where the first cell value does not equal a list of values as defined below.
But this marcro is deleting even these columns. I only want to delete columns where the first row in each column does not equal the criteria listed, i.e if cell D1 has the value "Australia" in it I don't want to delete it.
THe range I want to evaluate is C1 to AW1.
Can't figure out why it's not working, hope someone can help.
Thanks,
Hamond
Sub Deletecolums_Conditional()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim i As Long
For i = 47 To 3 Step -1
If Cells(1, i).Value <> "Australia" _ 'do not delete
Or Cells(1, i).Value <> "Canada" _
Or Cells(1, i).Value <> "Denmark" _
Or Cells(1, i).Value <> "G7" _
Or Cells(1, i).Value <> "NAFTA" _
Or Cells(1, i).Value <> "OECD + Major Six NME" _
Or Cells(1, i).Value <> "United States" Then
Cells(1, i).EntireColumn.Delete
End If
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
I have come up with the macro below to delete columns that do not meet multiple criteria. Basically I want to delete all columns in a given range where the first cell value does not equal a list of values as defined below.
But this marcro is deleting even these columns. I only want to delete columns where the first row in each column does not equal the criteria listed, i.e if cell D1 has the value "Australia" in it I don't want to delete it.
THe range I want to evaluate is C1 to AW1.
Can't figure out why it's not working, hope someone can help.
Thanks,
Hamond
Sub Deletecolums_Conditional()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim i As Long
For i = 47 To 3 Step -1
If Cells(1, i).Value <> "Australia" _ 'do not delete
Or Cells(1, i).Value <> "Canada" _
Or Cells(1, i).Value <> "Denmark" _
Or Cells(1, i).Value <> "G7" _
Or Cells(1, i).Value <> "NAFTA" _
Or Cells(1, i).Value <> "OECD + Major Six NME" _
Or Cells(1, i).Value <> "United States" Then
Cells(1, i).EntireColumn.Delete
End If
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub