Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Filter Problem

Posted on 2014-01-29
8
Medium Priority
?
250 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 33

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 33

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 33

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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

604 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