Solved

Filter Problem

Posted on 2014-01-29
8
216 Views
Last Modified: 2014-02-05
Hi Guys, when I put a Filter on Row 4 of the attachment, it stretches to the very end of the spreadsheet, instead of column BA where the data stops. Does anyone know why this is occuring?
DummyRec9.xlsm
0
Comment
Question by:Justincut
  • 3
  • 3
  • 2
8 Comments
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39818236
How are you instigating the filter?

Are you selecting the whole row or juat data within the row.

If you select row 4 by clicking on the row header, ie select the whole row, the filter applies to the whole row.

If you select a cell within the data, the Filter determines where to apply by selecting the region that contains it. The issue you have with your data is that you have items above the headers (columns S to AJ) and the automagic application of the filter finds a "bridge" between the data and these cells so determines it is part of the data.

Thanks
Rob H
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39818262
Next question, no doubt, how do I get it to work how I want it?

Couple of options:

1) Insert a blank row above the headers, then there is no bridge to the cells above the data.
2) Select the data requiring the filter. I was able to select the data in just a few keyboard clicks. Select A4 and then Press Shift and End and Down arrow. This selects all data in column A. Now keep Shift pressed and press Space bar This will now select the whole row for all data selected in column A. Now keep Shift pressed and press End then Left arrow. Data region selected.

Thanks
Rob H
0
 

Author Comment

by:Justincut
ID: 39818352
Hi, I am selecting the whole row, but I have always done the same and its only selected the cells with the data eg. up to column BA. See the attachment below.
Any ideas?
DummyRec12.xlsx
0
 

Author Comment

by:Justincut
ID: 39818689
I suspect there's random data in distant cells on the end of the Excel spreadsheet as my file has been getting bigger. Is there a good way to get rid of this random data?
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 31

Expert Comment

by:Rob Henson
ID: 39819097
If you know there is supposed to be nothing beyond BA then select the whole columns BB and beyond and press delete. If you use the keyboard Alt E A it will also clear formatting.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39819522
To check if there are random data in distant cells, move the scrollbars to the bottom and right. If you can still see data on the screen, then you don't have the random data problem. On the other hand, if you see mostly blank cells with perhaps a few pieces of irrelevant data, then you can probably delete the rows below your data all the way to the 1048576 and columns to the right of your data all the way to XFD.

I didn't observe any such problem in the workbook you posted.

I did see some unusual values (the word "new" instead of a date) in cells AJ1049:AJ1050. Those text values caused other formulas on that row to return error values.
0
 

Author Comment

by:Justincut
ID: 39821794
Can you tell me why in my 2nd attachment, the filter only goes to column BA when it has the same visible data as the first one?
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39821836
I tested your most recent attachment with the following results in Excel 2013:
1.  Select A1, then turn on AutoFilter. AutoFilter range is just A1:A2
2.  Select A4, then turn on AutoFilter. AutoFilter range is A1:AX1050. It is broken by blank column AY.
3.  Select row 1, then turn on AutoFilter. AutoFilter range is A1:BB2. I wouldn't have guessed this result.
4.  Select row 4, then turn on AutoFilter. AutoFilter range is A4:BB1050. It is broken by absence of data past column BB.
5.  Select row 5, then turn on AutoFilter. AutoFilter range is A5:BB1050. It is broken by absence of data past column BB.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now