Solved: Conditional column delete based on multiple criteria

[Hamond]

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

[VBA]
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
[/VBA]

[mdmackillop]

I would go with Select Case, rather than a list of OR.

[Hamond]

Thanks for the suggestion mdmackillop. I have mocked up a dummy version below where the Zs in the case selects (z4, z7 etc) are the columns I want to keep, all other columns I want to delete.

How do I tell it to ignore/do nothing with these columns? Currently I have put in "resume next" but this does not seem to work. Is there a vba syntax for do nothing/ignore?

Thanks,

Hamond
-------------------------------------------------

[VBA]
Sub SelectCase()
Dim i As Long
For i = 10 To 2 Step -1
Select Case Cells(i, 1).Value
Case "Z4"
Resume Next
Case "Z7"
Resume Next
Case "Z9"
Resume Next
Case "Z2"
Resume Next

Case Else
Cells(i, 1).EntireColumn.Delete
End Select
Next i
End Sub
[/VBA]

[mancubus]

[vba]Cells(i, 1)[/vba]
loops thru cells in column 1 only.


is one cell populated with any of the specified strings (Zs) in the column sufficient to keep that column?

[mancubus]

if this the scenario then you may test the below code with a copy of your file.

[vba]
Sub SelectCase()

Dim rng As Range
Dim LastRow As Long, LastCol As Long, Col As Long

LastRow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
LastCol = Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column

For Col = LastCol To 1 Step -1
Set rng = Range(Cells(2, Col), Cells(LastRow, Col))
If Application.CountIf(rng, "Z4") > 0 Then
ElseIf Application.CountIf(rng, "Z7") > 0 Then
ElseIf Application.CountIf(rng, "Z9") > 0 Then
ElseIf Application.CountIf(rng, "Z2") > 0 Then
Else
Columns(Col).Delete
End If
Next Col

End Sub
[/vba]

[Hamond]

Sorry, I had it the wrong around - should have read (1, i)!

Anyway, Mancubus, I have tested your code and this works fine. But I would still be interested in seeing what the select case version would look like as I am sure it would be useful for other applications.

Thanks,

Hamond

[mdmackillop]

You can do multiple checks in one statement. FYI, the Select Case is exited as soon as a match is found.
[vba]Sub SelectCase()
Dim i As Long
For i = 10 To 2 Step -1
Select Case UCase(Cells(1, i).Value)
Case "Z4", "Z7", "Z9", "Z2"
'do nothing
Case Else
Cells(1, i).EntireColumn.Delete
End Select
Next i
End Sub
[/vba]

[Hamond]

Thanks MdMacKillop,

This code works as expected.

Hamond