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

We applied a filter to a dataset that has a blank row, but it stopped filtering when it encountered the blank row.

excel filter stops at blank row


Fix 1 – Selecting the Whole Range

Steps:

  • Select all the cells from B4 to C10 and go to the Data tab on top of the screen.
  • Click on Filter under Sort & Filter.

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

  • 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


Fix 2 – Using the COUNTBLANK Function

Steps:

  • Insert the following formula in cell D5:
=COUNTBLANK(B5:C5)

countblank function to solve excel filter stops at blank row problem

  • Press Enter and copy this formula down using the Fill Handle.

countblank function to solve excel filter stops at blank row problem

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

  • This will add the filter option to this dataset.

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

  • This will remove all the blank cells from the filter.


Fix 3 – Sorting the Dataset

Steps:

  • 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

  • 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


Fix 4 – Applying VBA Code

Steps:

  • Go to the Developer tab and select Visual Basic.

vba code to solve excel filter stops at blank row problem

  • Select Insert in the VBA window and click on Module.

  • Insert the following code in the module:
Public Sub FilterBlank()
Worksheets("VBA").Range("B4:C10").AutoFilter
End Sub

  • Go to the Developer tab and click Macros.

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

  • The VBA code will apply the necessary filtering.


How to Fix Excel Filter Is Not Working with Merged Cells

Steps:

  • Select the merged cells and click on the Merge & Center icon as in the image below.
  • 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 in the Advanced Filter in Excel

Steps:

  • Go to cell E6 and insert the following formula:
=C5<>""

  • Press the Enter key from the keyboard.
  • Select the cells from B4 to C10 and click on Advanced under Sort & Filter.

  • Fill up the List range and Criteria range as below and click OK.

  • This will remove any blank cells present in the dataset.


Download the Practice Workbook


<< Go Back to Filter in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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