[Fixed!] Excel Filter Stops at Blank Row (4 Possible Solutions)

In this tutorial, I am going to show you 4 possible solutions to excel filter stops at blank row. You can use these fixes in your own dataset to find out blank cells and include them in the filter. Throughout this tutorial, you will also learn some important excel tools and techniques which will be very useful in any excel related task.

Download Practice Workbook

You can download the practice workbook from here.

4 Possible Solutions If Excel Filter Stops at Blank Row

We have taken a concise dataset to explain the steps clearly. The dataset has approximately 7 rows and 2 columns. Initially, we are keeping all the cells in Accounting format. For all the datasets, we have 2 unique columns which are Item and Price. Although we may vary the number of columns later on if that is needed.

1. Selecting Whole Range

The first solution we can try is to select the range of the dataset before applying the filter option. Follow the steps below to do this.

Steps:

• First, select all the cells from B4 to C10 and go to the Data tab on top of the screen.
• Now, click on Filter under Sort & Filter.

• As a result, you should get the filter option in the dataset including all the data rows.

2. Using COUNTBLANK Function

The COUNTBLANK function in excel can count the number of blank cells in a range of data that we specify as input. Let us see how to use this function to solve the excel filter stops at blank row problem.

Steps:

• To begin with, insert the following formula in cell D5:

• Next, press Enter and copy this formula down using Fill Handle.

• Now, select all the cells from B4 to D10 and click on Filter under Sort & Filter.

• Immediately, this will add the filter option to this dataset.

• Then, click on No. of Blank Cells drop-down and select only the 0 box from the filter options.

• Consequently, this will remove all the blank cells from the filter.

Read More: [Fixed] Excel COUNT Function Not Working

Similar Readings

• [Fixed!] Excel Scrolling Too Many Rows (2 Easy Solutions)
• [Solved]: Macro Settings Greyed out in Excel
• [Fixed!] Auto Fill Options Not Showing in Excel
• [Fixed!] IF Function Is Not Working in Excel (4 Quick Solutions)
• [Solved!]: Excel Page Layout Being Greyed Out (4 Quick Fixes)

3. Sorting Dataset

Another fix that we can try is to first sort out the dataset and then apply the filter option. Let us see how to do this in detail.

Steps:

• To start with this method, select all the cells from B4 to C10 and select A-Z sorting under Sort & Filter.

• As a result, this will separate the blank cells at the end of the dataset and then you can apply filtering to it.

Read More: Excel Data Validation Greyed Out (4 Reasons with Solutions)

4. Applying VBA Code

If you are familiar with VBA in excel, then you can solve this problem on hand just by writing a few lines of code. Below are the steps to do this.

Steps:

• For this method, go to the Developer tab and select Visual Basic.

• Now, select Insert in the VBA window and click on Module.

• Next, type in the formula below in the new window:

Public Sub FilterBlank()
Worksheets("VBA").Range("B4:C10").AutoFilter
End Sub

• Then, open the macro from the Developer tab by clicking on Macros.

• Now, in the Macro window, select the FilterBlank macro and click Run.

• As a result, the VBA code will apply the necessary filtering including all the data rows.

How to Fix When Excel Filter Is Not Working

In case your filtering option in excel is not working, then you can try the following steps to quickly fix that.

Steps:

• For this, select the merged cells and click on the Merge & Center icon as in the image below.
• Finally, this should solve the problem and you can now select the data cells to apply the filter.

Read More: [Solved:] Excel Not Filtering Entire Column (3 Quick Fixes)

How to Exclude Blank Cells Using Advanced Filter in Excel

The existence of unnecessary blank cells within our dataset can cause some problems at times. So it is better to remove any blank cells that are not important. We will see how to do this in the following steps.

Steps:

• To start with, go to cell E6 and type in the following formula:

• Now press the Enter key from the keyboard.
• Next, select the cells from B4 to C10 and click on Advanced under Sort & Filter.

• Here, fill up the List range and Criteria range as below and click OK.

• Finally, this will remove any blank cells present in the dataset.

Read More: [Solved] Excel Formatting Not Working Unless Double Click Cell

Conclusion

I hope that you were able to understand the methods that I showed in this tutorial on 4 possible solutions to excel filter stops at blank row problem. As you can see, there are quite a few ways to solve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.

Related Articles

• [Fixed!] Excel Hyperlink Keeps Coming Back (5 Quick Solutions)
• [Fixed!] Formulas Are Not Calculating Automatically in Excel
• [Fixed!] Macros Not Working in Excel (3 Possible Solutions)
• [Fixed!] Excel COUNTIF Function Not Working for String “True”
• [Fixed!] Unshare Workbook Greyed Out in Excel
• [Fixed] Excel Date Filter Not Working