Sub Delete_Based_on_Criteria()
' This macro will delete an entire row based on the presence of a
'predefined word or set of words. If that word or set of words is
'found in a cell, in a specified column, the entire row will be 'deleted
And EntireColumn.Delete method will delete the Entire Columns from the Excel spreadsheet. Delete Columns based on multiple criteria using VBA Now you can observe that the Columns are deleted from worksheet if cells in Range A1 to T1 contains 1 or blank cells. Explained VBA Code to Delete. Excel VBA code to Delete Entire Column example will help us to delete Columns in excel worksheet. ‘Starting the sub procedure to write VBA macro to delete Columns using VBA for loop Sub sbVBS_To_Delete_EntireColumn_For_Loop_C() ‘Declaring the.
' This macro will delete an entire row based on the presence of a
'predefined word or set of words. If that word or set of words is
'found in a cell, in a specified column, the entire row will be 'deleted
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim FoundRowToDelete As Boolean
Dim OriginalCalculationMode As Long
Dim RowsToDelete As Range
Dim SearchItems() As String
Dim Z As Long
Dim LastRow As Long
Dim FoundRowToDelete As Boolean
Dim OriginalCalculationMode As Long
Dim RowsToDelete As Range
Dim SearchItems() As String
Dim DataStartRow As Long
Dim SearchColumn As String
Dim SheetName As String
Dim SearchColumn As String
Dim SheetName As String
'''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''
' Choose the row you want the search and delete to start on
' Choose the column to search and delete to use for deletion
' Choose the sheet in the workbook you want this macro to be run on
' Choose the column to search and delete to use for deletion
' Choose the sheet in the workbook you want this macro to be run on
DataStartRow = 1
SearchColumn = 'B'
SheetName = 'Sheet1'
SearchColumn = 'B'
SheetName = 'Sheet1'
'''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''
' Enter the terms you want to be used for criteria for deletion
' All terms entered below are CASE SENSITIVE and need to be
'seperated by a comma
' All terms entered below are CASE SENSITIVE and need to be
'seperated by a comma
SearchItems = Split('INPUT TEXT HERE, INPUT TEXT HERE', ',')
'''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''
On Error GoTo Whoops
OriginalCalculationMode = Application.Calculation
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
OriginalCalculationMode = Application.Calculation
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, SearchColumn).End(xlUp).Row
For X = LastRow To DataStartRow Step -1
FoundRowToDelete = False
For Z = 0 To UBound(SearchItems)
If InStr(.Cells(X, SearchColumn).Value, SearchItems(Z)) Then
FoundRowToDelete = True
Exit For
End If
Next
If FoundRowToDelete Then
If RowsToDelete Is Nothing Then
Set RowsToDelete = .Cells(X, SearchColumn)
Else
Set RowsToDelete = Union(RowsToDelete, .Cells(X, SearchColumn))
End If
If RowsToDelete.Areas.Count > 100 Then
RowsToDelete.EntireRow.Delete
Set RowsToDelete = Nothing
End If
End If
Next
End With
If Not RowsToDelete Is Nothing Then
RowsToDelete.EntireRow.Delete
End If
LastRow = .Cells(.Rows.Count, SearchColumn).End(xlUp).Row
For X = LastRow To DataStartRow Step -1
FoundRowToDelete = False
For Z = 0 To UBound(SearchItems)
If InStr(.Cells(X, SearchColumn).Value, SearchItems(Z)) Then
FoundRowToDelete = True
Exit For
End If
Next
If FoundRowToDelete Then
If RowsToDelete Is Nothing Then
Set RowsToDelete = .Cells(X, SearchColumn)
Else
Set RowsToDelete = Union(RowsToDelete, .Cells(X, SearchColumn))
End If
If RowsToDelete.Areas.Count > 100 Then
RowsToDelete.EntireRow.Delete
Set RowsToDelete = Nothing
End If
End If
Next
End With
If Not RowsToDelete Is Nothing Then
RowsToDelete.EntireRow.Delete
End If
Whoops:
Application.Calculation = OriginalCalculationMode
Application.ScreenUpdating = True
Application.Calculation = OriginalCalculationMode
Application.ScreenUpdating = True
End Sub