Getting rid of specific cells could prove challenging if you were to do it manually. In this article, we're going to show you how to make Excel delete rows with value of your choosing, using VBA. You can remove cells with certain strings or create an input cell where you can enter a value to select which cells to remove.
A VBA code to do this will consist of 2 parts: determining the variables, and deleting the rows that where the condition is met. We present you 2 alternative approaches for both parts.
Variables
The variables are the range that test will be applied and the condition itself. You can choose to determine these variables by entering them in the code or create an input section to let the users do this. Entering a static value into the code is easier if you're consistently working with same range. On the other hand, if range changes, users will have to update the code every time. Here are both approaches for the range F3:F16 and condition the text set to "No":
Static variable in the code:
Set condition_range = Range("F3:F16")
condition = "No"
Dynamic variable through user input:
Set condition_range = Application.InputBox(Prompt:="Please select the range that condition will be tested:", Type:=8)
condition = Application.InputBox(Prompt:="Please type the condition text:")
Here, the user entries can be collected with the Application.InputBox control and we must also set the Type argument to 8 to select all ranges. If the Type argument is omitted, the default input type will be Text.
We recommend using an error handling method in your code to handle the case of a user pressing Cancel in the InputBox instead of providing the requested information. Our error handling approach uses 3 types of code blocks:
Error handling part before End Sub:
error_handling:
MsgBox ("Process is canceled.") 'Explanatory message for end-user
End Sub
Move the process to error handling code part:
On Error GoTo error_handling
Reset error handling procedure:
On Error GoTo 0
Rows
You have 2 options to determine the rows to be deleted. You can either perform a loop through the rows and apply a logical condition test, or use the VBA support of the AutoFilter feature to filter the rows that meet the condition. The decision criteria between two approaches will determine whether the data will be kept.
The AutoFilter feature won't work if your range is defined as an Excel Table. As a result, we suggest you to return your table into a regular range before running AutoFilter code. On the other hand, if your data spans over thousands of rows, looping through rows will require more computer resources and take longer to process. Let's see code blocks for both approaches:
Looping through rows:
With condition_range
For i = .SpecialCells(xlCellTypeLastCell).Row To .Row Step -1
If Cells(i, .Column) = condition Then Rows(i).EntireRow.Delete
Next i
End With
AutoFilter:
condition_range.AutoFilter Field:=1, Criteria1:=condition
Rows(condition_range.EntireRow.Address).Delete Shift:=xlUp
First, you need to add the module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.
Delete rows by a static condition with loop
Sub DeleteRowsByStaticCondition_Loop()
'defining variables
Dim condition_range As Range
Dim condition As String
Dim i As Integer
'populating variables
Set condition_range = Range("F:F")
condition = "No"
'loop through rows and test the condition
With condition_range
For i = Cells(.SpecialCells(xlCellTypeLastCell).Row, .Column).Row To 1 Step -1
If Cells(i, .Column) = condition Then Rows(i).EntireRow.Delete
Next i
End With
End Sub
Delete rows by a dynamic condition with loop
Sub DeleteRowsByUserCondition_Loop()
'defining variables
Dim condition_range As Range
Dim condition As String
Dim i As Integer
'error handling for Cancel buttons of InputBox controls
On Error GoTo error_handling
'populating variables
Set condition_range = Application.InputBox(Prompt:="Please select the range that condition will be tested:", Type:=8)
condition = Application.InputBox(Prompt:="Please type the condition text:")
'reset error handling procedure
On Error GoTo 0
'loop through rows and test the condition
With condition_range
For i = .SpecialCells(xlCellTypeLastCell).Row To .Row Step -1
If Cells(i, .Column) = condition Then Rows(i).EntireRow.Delete
Next i
End With
'exit sub without running error handling codes
Exit Sub
'error handling
error_handling:
MsgBox ("Process is canceled.") 'Explanatory message for end-user
End Sub
Delete rows by a static condition with AutoFilter
Sub DeleteRowsByStaticCondition_AutoFilter()
'defining variables
Dim condition_range As Range
Dim condition As String
'populating variables
Set condition_range = Range("$F:$F")
condition = "No"
'applying AutoFilter
condition_range.AutoFilter Field:=1, Criteria1:=condition
'deleting filtered rows, remaining rows will be shifted to up
Rows(condition_range.EntireRow.Address).Delete Shift:=xlUp
'removing AutoFilter
On Error Resume Next
ActiveSheet.condition_range.ShowAllData
'selecting a cell for end-user
condition_range.Cells(1, 1).Select
End Sub
Delete rows by a dynamic condition with AutoFilter
Sub DeleteRowsByUserCondition_AutoFilter()
'defining variables
Dim condition_range As Range
Dim condition As String
'populating variables
Set condition_range = Application.InputBox(Prompt:="Please select the range that condition will be tested:", Type:=8)
condition = Application.InputBox(Prompt:="Please type the condition text:")
'applying AutoFilter
condition_range.AutoFilter Field:=1, Criteria1:=condition
'deleting filtered rows, remaining rows will be shifted to up
Rows(condition_range.EntireRow.Address).Delete Shift:=xlUp
'removing AutoFilter
On Error Resume Next
ActiveSheet.condition_range.ShowAllData
'selecting a cell for end-user
condition_range.Cells(1, 1).Select
End Sub
