Macro needed to delete columns where one of two strings not present

[jmclaren]

Hi. I regularly receive a spreadsheet that has several hundred columns and a similar number of rows. From column 'D' onwards, the only columns I'm interested in are ones that contain either string "Outstanding" or "Required". Can anyone suggest a macro that would delete all columns after 'D' that don't contain one or other of these strings in at least one of the rows? Thanks.

 

[JoeMo]

Do you want the search for those two strings to be case sensitive?

 

[jmclaren]

Do you want the search for those two strings to be case sensitive?

Hi Joe. The case will always be consistent, so if it helps to be precise, including case then that's fine - doesn't really matter though. Thanks.

 

[JoeMo]

Hi Joe. The case will always be consistent, so if it helps to be precise, including case then that's fine - doesn't really matter though. Thanks.

Try this:

Sub DeleteIfNeither()
Const S1 As String = "Outstanding"
Const S2 As String = "Required"
Dim F As Range
Application.ScreenUpdating = False
For i = ActiveSheet.UsedRange.Columns.Count To 5 Step -1
    Set F = Columns(i).Find(S1, LookAt:=xlPart)
    If F Is Nothing Then
        Set F = Columns(i).Find(S2, LookAt:=xlPart)
        If F Is Nothing Then Columns(i).Delete
    End If
Next i
Application.ScreenUpdating = True
End Sub

 

[jmclaren]

Joe,

Thanks for that - it works perfectly . Much appreciated!

Jim

 

[JoeMo]

Joe,

Thanks for that - it works perfectly . Much appreciated!

Jim

You are welcome - thanks for the reply.