Filter leaves last row of data visible regardless of what is filtered

[frank_AL]

I have a test file from my customer that I am using to develop a program for him. As part of the program I filter the data based on Customer; Column B. The problem I am having is regardless of how I filter, the last row of data; Row 125 remains visible. As I was preparing the file to be posted here I put the value 0 in Column C and then copied it across all remaining columns to Column AN. After doing this, I checked that the filter error still existed but the error had cleared. Any filter I tested at that point filtered as expected. So, I undid the copy across to all columns and the filter error reoccurred.

At that point, I started copy column C with all zeroes 1 column at a time and then rechecking for the filter error. From column D to column Y when I copied the zeroes the error remained. However, when I copied the zeroes to column Z "New Commission Cost" the error cleared. I have checked the formula to ensure it is consistent down the entire column. I also define a named range that encompassed all data and that had no impact on the results experienced.

FYI, the normal process in my program is to sort on the Account Manager name in column A before filtering in column B. I have changed all names to John Smith so that employee names are not exposed. I saw no way to sanitize the Customer names without impacted the specific issue I am experiencing.

For the life of me I can't figure out what is causing this problem. Any and all help would be greatly appreciated.

In order to get my mini file small enough to post I had to delete quite a few columns so the column in question is now column D. I have verified on this smaller version of the file the problem still exists and acts exactly as I have described.

Price Change File.xlsx
	A	B	C	D	E	F
1	Account Manager	Customer	Measure	Other	New Commission Cost	$ CC change
2	John Smith	3D Corporate Solutions	0	0	$ -	#REF!
3	John Smith	3D Corporate Solutions	0	0	$ -	#REF!
4	John Smith	Advance Pierre Foods, Inc. Enterprise Plant	0	0	$ -	#REF!
5	John Smith	Advance Pierre/Allied Specialty Foods	0	0	$ -	#REF!
6	John Smith	Ajinomoto Foods	0	0	$ -	#REF!
7	John Smith	ALLIANCE WATER RESOURCES	0	0	$ -	#REF!
8	John Smith	ALLIANCE WATER RESOURCES	0	0	$ -	#REF!
9	John Smith	Alma Water Department	0	0	$ -	#REF!
10	John Smith	Amerex Corporation	0	0	$ -	#REF!
11	John Smith	ATOKA RURAL WATER #4	0	0	$ -	#REF!
12	John Smith	Butterball, LLC	0	0	$ -	#REF!
13	John Smith	CHEROKEE CO RWD #16	0	0	$ -	#REF!
14	John Smith	City of Bay Springs Industrial Park Treatment Fac	0	0	$ -	#REF!
15	John Smith	City of Bay Springs Industrial Park Treatment Fac	0	0	$ -	#REF!
16	John Smith	City of Fort Smith	0	0	$ -	#REF!
17	John Smith	City of Fort Smith	0	0	$ -	#REF!
18	John Smith	City of Meridian, WWTP	0	0	$ -	#REF!
19	John Smith	Clarksville Connected Utilities	0	0	$ -	#REF!
20	John Smith	CONWAY CO REGIONAL WATER	0	0	$ -	#REF!
21	John Smith	Cooper Farms Processing	0	0	$ -	#REF!
22	John Smith	Creekstone Farms Premium Beef, LLC	0	0	$ -	#REF!
23	John Smith	Diversified Foods and Seasonings LLC	0	0	$ -	#REF!
24	John Smith	Dunn Paper	0	0	$ -	#REF!
25	John Smith	Dunn Paper	0	0	$ -	#REF!
26	John Smith	Dunn Paper	0	0	$ -	#REF!
27	John Smith	Emmi Roth USA, Inc	0	0	$ -	#REF!
28	John Smith	EUFAULA, CITY OF	0	0	$ -	#REF!
29	John Smith	FT GIBSON, TOWN OF	0	0	$ -	#REF!
30	John Smith	Georges, Inc.	0	0	$ -	#REF!
31	John Smith	Golden Rod Broilers, Inc.	0	0	$ -	#REF!
32	John Smith	GRAND RIVER DAM AUTHORITY (GRDA)	0	0	$ -	#REF!
33	John Smith	Guntersville Water and Sewer	0	0	$ -	#REF!
34	John Smith	HarChem Water Services	0	0	$ -	#REF!
35	John Smith	Hiland Dairy Foods	0	0	$ -	#REF!
36	John Smith	House of Raeford - Nesmith	0	0	$ -	#REF!
37	John Smith	Huntsville Water Utilities	0	0	$ -	#REF!
38	John Smith	Hydro Solutions	0	0	$ -	#REF!
39	John Smith	INFRAMARK MCALESTER	0	0	$ -	#REF!
40	John Smith	InTerraChem, LLC	0	0	$ -	#REF!
41	John Smith	Klondike Cheese Co	0	0	$ -	#REF!
42	John Smith	Koch Food of Ashland LLC	0	0	$ -	#REF!
43	John Smith	Koch Foods of Cincinnati	0	0	$ -	#REF!
44	John Smith	Koch Foods of MS LLC	0	0	$ -	#REF!
45	John Smith	Krebs Utilities Authority	0	0	$ -	#REF!
46	John Smith	LONGTOWN RW&S DISTRICT #1	0	0	$ -	#REF!
47	John Smith	Lonoke White PWA	0	0	$ -	#REF!
48	John Smith	Lonoke White PWA	0	0	$ -	#REF!
49	John Smith	Mars Petcare US ARK	0	0	$ -	#REF!
50	John Smith	Metal Coaters	0	0	$ -	#REF!
51	John Smith	Mr. Dells Foods, LLC	0	0	$ -	#REF!
52	John Smith	MULBERRY, CITY OF	0	0	$ -	#REF!
53	John Smith	MUSKOGEE CITY OF	0	0	$ -	#REF!
54	John Smith	Nebraska City Utilities	0	0	$ -	#REF!
55	John Smith	Nebraska City Utilities	0	0	$ -	#REF!
56	John Smith	ONE TIME CUSTOMER	0	0	$ -	#REF!
57	John Smith	Ozark Water Department	0	0	$ -	#REF!
58	John Smith	Peco Foods	0	0	$ -	#REF!
59	John Smith	Peco Foods	0	0	$ -	#REF!
60	John Smith	Peco Foods	0	0	$ -	#REF!
61	John Smith	Peco Foods	0	0	$ -	#REF!
62	John Smith	Peco Foods	0	0	$ -	#REF!
63	John Smith	Peco Foods West Point	0	0	$ -	#REF!
64	John Smith	Perdue Farms LLC - Cromwell	0	0	$ -	#REF!
65	John Smith	Perdue Farms, Inc - Georgetown	0	0	$ -	#REF!
66	John Smith	Pilgrims Pride - Athens GA Processing Facility	0	0	$ -	#REF!
67	John Smith	Pilgrim's Pride Corp - DeQueen	0	0	$ -	#REF!
68	John Smith	Plymouth Tube Co	0	0	$ -	#REF!
69	John Smith	Processors, Inc.	0	0	$ -	#REF!
70	John Smith	Processors, Inc.	0	0	$ -	#REF!
71	John Smith	Processors, Inc.	0	0	$ -	#REF!
72	John Smith	S.O.W.C	0	0	$ -	#REF!
73	John Smith	Sanderson Farms	0	0	$ -	#REF!
74	John Smith	Sanderson Farms	0	0	$ -	#REF!
75	John Smith	Sanderson Farms	0	0	$ -	#REF!
76	John Smith	Sanderson Farms	0	0	$ -	#REF!
77	John Smith	Sanderson Farms	0	0	$ -	#REF!
78	John Smith	Sanderson Farms	0	0	$ -	#REF!
79	John Smith	Sanderson Farms	0	0	$ -	#REF!
80	John Smith	Sanderson Farms	0	0	$ -	#REF!
81	John Smith	Sanderson Farms	0	0	$ -	#REF!
82	John Smith	Sanderson Farms, Inc.	0	0	$ -	#REF!
83	John Smith	SARDIS LAKE WATER AUTHORITY	0	0	$ -	#REF!
84	John Smith	Schwan's Global Supply Chain	0	0	$ -	#REF!
85	John Smith	Sentinel Solutions Inc	0	0	$ -	#REF!
86	John Smith	Sentinel Solutions Inc	0	0	$ -	#REF!
87	John Smith	Siloam Springs Water & Wastewater	0	0	$ -	#REF!
88	John Smith	Southern Hens, Inc.	0	0	$ -	#REF!
89	John Smith	Stage 3 Separation, LLC	0	0	$ -	#REF!
90	John Smith	Stage 3 Separation, LLC	0	0	$ -	#REF!
91	John Smith	Twin Rivers Foods, Inc. - Atkins	0	0	$ -	#REF!
92	John Smith	Tyson Foods, Inc	0	0	$ -	#REF!
93	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
94	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
95	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
96	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
97	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
98	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
99	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
100	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
101	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
102	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
103	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
104	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
105	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
106	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
107	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
108	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
109	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
110	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
111	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
112	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
113	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
114	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
115	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
116	John Smith	Tyson Foods, Inc.	0	0	$ -	#REF!
117	John Smith	Tyson Foods, Inc. Berry Street	0	0	$ -	#REF!
118	John Smith	Tyson Foods, Inc. Chicken Quick	0	0	$ -	#REF!
119	John Smith	Tyson River Valley Animal Foods - Forest	0	0	$ -	#REF!
120	John Smith	Utilities Inc of Louisiana	0	0	$ -	#REF!
121	John Smith	Utilities Inc of Louisiana	0	0	$ -	#REF!
122	John Smith	VEOLIA REDOAK-LATIMER	0	0	$ -	#REF!
123	John Smith	WATER DEPOT	0	0	$ -	#REF!
124	John Smith	WATER DEPOT	0	0	$ -	#REF!
125	John Smith	WATER DEPOT	0	0	$ -	#REF!
Blended Sheet

Cell Formulas
Range	Formula
E2:E125	E2	=SUBTOTAL(9,D2:D2)
F2:F125	F2	=E2-#REF!

 

[RoryA]

I believe the problem is your use of SUBTOTAL formulas. If memory serves, you can fix that by amending it to something like:

=0+SUBTOTAL(9:D2:D2)

 

[frank_AL]

I believe the problem is your use of SUBTOTAL formulas. If memory serves, you can fix that by amending it to something like:

=0+SUBTOTAL(9:D2:D2)

Rory, you are correct, I made the change in the formula and that resolved the Filter issue I had been experiencing. Thank you so much for being willing to share your outstanding technical expertise! But I'll have to say it boggles my mind that that problem in Column Z was causing a filtering problem in Column B. Nevertheless thanks again!