This post covers different ways to delete rows based on a cell value in Excel (or delete based on conditions).
The following four methods to delete rows based on cell value are covered in this video:
1) Filtering the data and deleting the rows that match the filter criteria
2) Sorting the rows alphabetically and then deleting the rows that are grouped together and match the criteria
3) Finding the cells that have a specific value and then deleting these rows
4) Using VBA Autofilter to filter and delete rows based on cell value using VBA
The method you choose can depend on how your data is structured,
Note that when you delete a row, it also deletes any content that may be on the right or left of that rows. In case you want to delete the data in a dataset but keep the other content in the row intact, you can use the SORT method covered in the video.
The find and replace method also allows you to use wild-card characters when finding cells. For example, if you want to find all cells where the region is Mid-West or South-West, you can use *-west. The asterisk (*) will represent any number of characters and this criterion will select all the cells where the text ends with -west.
Code to Delete all rows where the region is Mid-West
Sub DeleteRowsWithSpecificText()
‘Source:https://ift.tt/2JZ0lzY
ActiveCell.AutoFilter Field:=2, Criteria1:=”Mid-West”
ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete
End Sub
Code to Delete records the region is Mid-West but not delete the entire row:
Sub DeleteRowsWithSpecificText()
‘Source:https://ift.tt/2JZ0lzY
Dim MsgboxAns As Integer
ActiveCell.AutoFilter Field:=2, Criteria1:=”Mid-West”
MsgboxAns = MsgBox(“Are you sure you want to delete these cells?”, vbYesNo + vbQuestion)
If MsgboxAns = vbYes Then
ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete (xlShiftUp)
End If
End Sub
Code to delete rows based on a cell value (in an EXCEL TABLE)
Sub DeleteRowsinTables()
‘Source:https://ift.tt/2JZ0lzY
Dim Tbl As ListObject
Set Tbl = ActiveSheet.ListObjects(1)
ActiveCell.AutoFilter Field:=2, Criteria1:=”Mid-West”
Tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
End Sub
#Excel #ExcelTips #ExcelTutorial