PDA

View Full Version : Solved: VBA to Delete Column based on Header



JimS
07-17-2009, 09:37 AM
Any obvious reason that this code does not run?

I'm trying to delete every column that has "ABC" somewhere in the Header (Row - 1), on every sheet that's name begins with "Sheet".

Thanks for any and all help...

Jim

Sub DeleteABC()

Dim A As Range

For Each sht In ActiveWorkbook.Sheets
If Left(sht.Name, 5) = "Sheet" Then
On Error Resume Next

Do
Set A = Rows(1).Find(What:="ABC", LookIn:=xlValues, lookat:=xlPart)
If A Is Nothing Then Exit Do
A.EntireColumn.Delete
Loop

On Error GoTo 0

End If

Next sht

End Sub

Bob Phillips
07-17-2009, 09:47 AM
Sub DeleteABC()

Dim A As Range
Dim sht As Worksheet

For Each sht In ActiveWorkbook.Sheets

If Left(sht.Name, 5) = "Sheet" Then
On Error Resume Next

Do
Set A = sht.Rows(1).Find(What:="ABC", LookIn:=xlValues, lookat:=xlPart)
If A Is Nothing Then Exit Do
A.EntireColumn.Delete
Loop

On Error GoTo 0
End If
Next sht

End Sub

JimS
07-17-2009, 10:12 AM
Thanks and sorry, not sure how I missed that...

Bob Phillips
07-17-2009, 11:07 AM
Thanks and sorry, not sure how I missed that...

We all do it at some point.