All of the rows in range (B3:E8) that contained text have been deleted. This image shows the final outcome of the process explained above. Note: to delete the visible rows, right-click on any of the selected cells and click Delete row. Click on Find & Select in the Editing group. Note: in this example we are selecting rows 3 to 8.ġ0. Select all of the rows in the data (filtered) range. Filter for Text by clicking of the filter box in the Text/No Text cell (F2) and selecting only the Text checkbox.ĩ. Click on Sort & Filter in the Editing group.ħ. Note: in this example we are selecting range (B2:F8).ĥ. Select the data range, including the formula and the associated headings.
Read this post to findout how to delete blank rows / cells from your excel data in a snap. Today we will learn a very simple trick to delete blank rows from excel spreadsheets. This is very common when you try to import data from somewhere else (like a text file or a CSV file). We have also inserted a column heading, Text/No Text, in cell F2 associated with formula that is applied.ģ. Blank rows or Blank cells is a problem we all inherit one time or another. Note: in this example we are applying the formula for rows 3 to 8. Apply the formula across all of the rows associated with the range. The data_row_rng is a range that represents a single row from a data range that you are testing for text.Ģ. Enter this formula into the column against the first data row (=IF(COUNTIF(data_row_rng,"*")>0,"Text","No Text")). Scroll to the column next to the last data column, which in this example will be column F. This image shows the starting point without any adjustments.ġ. In this example we will be deleting entire rows if there is text in any of the rows, in range (B3:E8). Now we have a clean list where all of the rows contain a value for Last login.
Then select Entire row, and click the OK button. Now carefully right-mouse click on one of the empty cells, and choose Delete from the menu. 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 Excel has now selected all of the blank cells in the column.