Delete entire row if contains text
How to delete an entire row if text is contained in a selected range using Excel and VBA methods
Enter formula in the column next to the last data column > Apply the formula across relevant rows > Select the range, including the formula > Home tab > Editing group > Click Sort & Filter > Click Filter > Filter for Text > Select all the rows within the range > Editing > Click Find & Select > Click Go To Special > Select Visible cells only > Click OK > Delete rows
| In this example we will be deleting entire rows if there is text in any of the rows, in range (B3:E8). This image shows the starting point without any adjustments. |  |
| 1. Scroll to the column next to the last data column, which in this example will be column F. Enter this formula into the column against the first data row (=IF(COUNTIF(data_row_rng,"*")>0,"Text","No Text")). The data_row_rng is a range that represents a single row from a data range that you are testing for text. 2. Apply the formula across all of the rows associated with the range. Note: in this example we are applying the formula for rows 3 to 8. We have also inserted a column heading, Text/No Text, in cell F2 associated with formula that is applied. |
 |
| 3. Select the data range, including the formula and the associated headings. Note: in this example we are selecting range (B2:F8). |
 |
| 4. Select the Home tab. |  |
| 5. Click on Sort & Filter in the Editing group. 6. Click on Filter. |
 |
| 7. Filter for Text by clicking of the filter box in the Text/No Text cell (F2) and selecting only the Text checkbox. 8. Click OK. |
 |
| 9. Select all of the rows in the data (filtered) range. Note: in this example we are selecting rows 3 to 8. |
 |
| 10. Click on Find & Select in the Editing group. 11. Click on Go To Special. |
 |
| 12. Select Visible cells only. 13. Click OK. |
 |
| 14. Delete visible rows. Note: to delete the visible rows, right-click on any of the selected cells and click Delete row. |
 |
| This image shows the final outcome of the process explained above. All of the rows in range (B3:E8) that contained text have been deleted. |  |
Sub Delete_entire_row_if_contains_text()
'declare variables
Dim ws As Worksheet
Dim iRow As Integer
Dim ws As Worksheet
Dim iRow As Integer
Set ws = Worksheets("Analysis")
'delete rows with text in a single column by looping through relevant rows
For iRow = ws.Range("B3:B8").Rows.Count To 1 Step -1
For iRow = ws.Range("B3:B8").Rows.Count To 1 Step -1
If Application.WorksheetFunction.IsText(Cells(iRow + 2, 2)) = True Then
ws.Cells(iRow + 2, 2).EntireRow.Delete
End If
ws.Cells(iRow + 2, 2).EntireRow.Delete
End If
Next
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Data Range: This VBA code assumes that all of the data in the worksheet is contained in range (B3:B8), therefore, all data is captured in one column.
Worksheet Name: Have a worksheet named Analysis.
Data Range: This VBA code assumes that all of the data in the worksheet is contained in range (B3:B8), therefore, all data is captured in one column.
ADJUSTABLE PARAMETERS
Data Range: Select the data range by changing the column reference number from 2 and the row range by changing the range associated with iRow (B3:B8).
Data Range: Select the data range by changing the column reference number from 2 and the row range by changing the range associated with iRow (B3:B8).
ADDITIONAL NOTES
Note 1: This VBA code assumes that all of the data is stored in column B.
EXPLANATION
This tutorial explains and provides step by step instructions on how to delete entire rows if any of the cells in the selected range contain text, using an Excel and VBA method.
This tutorial explains and provides step by step instructions on how to delete entire rows if any of the cells in the selected range contain text, using an Excel and VBA method.
Excel Method: Using Excel you can delete entire rows that contain text by initially identifying the rows that contain text, using this formula =IF(COUNTIF(data_row_rng,"*")>0,"Text","No Text"). Then you would filter for only the rows that contain text and delete the rows. The data_row_rng represents a single row range that you are testing if it contains text.
VBA Method: The VBA method uses the For Loop to loop through each specified row, which is row 3 through to row 8 in column B and delete any row that contains text.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Delete entire row if contains number | How to delete an entire row if a number is contained in a selected range using Excel and VBA methods |