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

Get FREE Advanced Excel Exercises with Solutions!

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


Excel Filter Stops at Blank Row: 4 Possible Solutions

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.

excel filter stops at blank row


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.

selecting whole range to solve excel filter stops at blank row problem

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

selecting whole range to solve excel filter stops at blank row problem


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:
=COUNTBLANK(B5:C5)

countblank function to solve excel filter stops at blank row problem

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

countblank function to solve excel filter stops at blank row problem

  • 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.


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.

sorting dataset to solve excel filter stops at blank row problem

  • 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: How to Filter Multiple Rows in Excel


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.

vba code to solve excel filter stops at blank row problem

  • 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: Excel Not Filtering Entire Column


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:
=C5<>""

  • 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.


Download Practice Workbook

You can download the practice workbook from here.


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. If you have any queries, please let me know in the comments.


<< Go Back to Not Working | Filter in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nazmul Hossain Shovon
Nazmul Hossain Shovon

Nazmul Hossain Shovon, a BUET graduate in Naval Architecture and Marine Engineering, embarked on his career with 8 months dedicated to the Exceldemy project's triumph. Transitioning into a Software Developer role, he specialized in web add-in development. At Exceldemy, he authored about 125 blog articles and solved many visitors’ problems, refining his writing skills and delving into Excel-related topics. With a primary passion for programming and software development, Shovon continually explores new horizons, fostering professional growth in his... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo