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
JustincutAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
byundtConnect With a Mentor Commented:
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
 
Rob HensonFinance AnalystCommented:
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 HensonFinance AnalystCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
 
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 HensonFinance AnalystCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.