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