Solved: Delete Certain Columns

[bradh_nz]

Hi, I have the following macro trying to delete all columns in a sheet that does not contain the following headings however it needs to run through 3 or four times to just end up with these 9 columns.

Any help appreciated

Sub DeleteThisColumn()
Dim ThisColumn As Range

For Each ThisColumn In Range(Range("a1"), Range("IV1").End(xlToLeft))

If ThisColumn <> "Process Cluster" Or _
ThisColumn <> "Process SBU" Or _
ThisColumn <> "Control Cluster" Or _
ThisColumn <> "P - Process Name" Or _
ThisColumn <> "P - Process Owner" Or _
ThisColumn <> "P - Risk Classification" Or _
ThisColumn <> "C - Control Name" Or _
ThisColumn <> "C - Workstream" Or _
ThisColumn <> "C - Application Supporting ABC" Then

ThisColumn.EntireColumn.Delete

End If
Next ThisColumn

End Sub

[rory]

Try this:
[VBA]Sub DeleteThisColumn()
Dim lngColumn As Long, lngLastCol As Long

lngLastCol = Range("IV1").End(xlToLeft).Column
For lngColumn = lngLastCol To 1 Step -1

Select Case Cells(1, lngColumn)
Case "Process Cluster", "Process SBU", "Control Cluster", "P - Process Name", _
"P - Process Owner", "P - Risk Classification", "C - Control Name", _
"C - Workstream", "C - Application Supporting ABC"

' Do nothing
Case Else
' Delete column
Cells(1, lngColumn).EntireColumn.Delete
End Select
Next lngColumn
End Sub
[/VBA]

if you are deleting columns/rows it is usually better to loop backwards through them, otherwise the index gets thrown out of whack (technical term) when items are deleted.
HTH
Rory

[bradh_nz]

Thanks alot, that worked fine

I thought it must have been something like working from the end