• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

Filter Problem

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
Justincut
Asked:
Justincut
  • 3
  • 3
  • 2
1 Solution
 
Rob HensonIT & Database AssistantCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
JustincutAuthor Commented:
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
Industry Leaders: 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!

 
JustincutAuthor Commented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
byundtCommented:
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
 
JustincutAuthor Commented:
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
 
byundtCommented:
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

Hire Technology Freelancers with Gigs

Work with 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.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now