In this article we will learn how to delete only filtered rows without the hidden rows in excel 2010.
While working with Excel, there could be situations where we need to delete certain rows after hiding some particular rows. We may want to hide certain rows which are not required for the current analysis and then filter the data based on some parameters and delete these rows. If we just select these rows and delete, there are chances that the hidden rows get deleted too, especially if there are hidden rows in between the filtered rows. Hence we need to follow a different method to delete the filtered rows.
The steps given below will remove visible rows of data only after applying the filter.
1) We have data regarding Movies as shown in the below picture. Along with the populated rows, we also have some blank rows in between. If we need to delete the blank rows one at a time, it can be time consuming. So let’s see how we can delete them easily.
2) We will apply a Filter, see the screenshot: – Go to the Data tab and click on Filter.
3) Click on the Home tab? click on Find & Select? click on Go To Special, see screenshot:
4) In the Go To Special dialog box, select Blanks, see screenshot:
5) All the Blank rows will be selected, see screenshot:
6) Right click the mouse and select Delete ? Entire row, see screenshot:
7) Click on the Ok button. The blank rows will be deleted and this is how the data looks, see screenshot:
8) Now lets see another situation. If we need to check for movies which have a rating of less than 8.9, we need to apply the Filter on IMDB_Rating, Movies using less than 8.9 as
the criteriasee screenshot:
9) Is less than will be already selelcted in the dropdown. Enter the value of 8.9 in the box next to it. See the screenshot:
10) This is how our data looks, see the screenshot:
11) Select the range starting from A3 & Right click on the mouse and select ?Delete, see the screenshot:
12) You will get a popup asking if you want to delete the entire sheet row. Click on the Ok button
13) In the Data Ribbon, click on Clear to remove the filter which was applied.
14) This is how our data looks, see the screenshot: