VBA Macro to Delete Columns with Specific Values (Excel 2010 CODE)

Sub delete_columns_with_values()

Dim calcmode As Long
Dim ViewMode As Long
Dim myStrings As Variant
Dim FoundCell As Range
Dim I As Long
Dim myRng As Range
Dim sh As Worksheet
‘We use the ActiveSheet but you can also use Sheets(“MySheet”)
Set sh = ActiveSheet

‘We search in row 1
Set myRng = sh.Range(“1:1”)

‘Add more search strings if you need
myStrings = Array(“search string 1”, “search string 2”)

With sh

‘We select the sheet so we can change the window view

‘If you are in Page Break Preview Or Page Layout view go
‘back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

‘Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False

‘We will search the values in MyRng in this example
With myRng

For I = LBound(myStrings) To UBound(myStrings)
Set FoundCell = myRng.Find(What:=myStrings(I), _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
‘Use xlPart If you want to search in a part of the FoundCell
‘If you use LookIn:=xlValues it will also delete rows with a
‘formula that evaluates to “Ron”
If FoundCell Is Nothing Then
Exit Do
End If
Next I

End With

End With
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *