Solved

Filter Problem

Posted on 2014-01-29
8
223 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 32

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 32

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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

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
 
LVL 32

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 81

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 81

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

776 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