+ Reply to Thread
Results 1 to 6 of 6

Excel won't filter whole column.

  1. #1
    Registered User
    Join Date
    02-16-2010
    Location
    Norway
    MS-Off Ver
    Excel 2007
    Posts
    10

    Excel won't filter whole column.

    Hi!

    I have a question regarding filtering of columns, hopefully someone is able to put me on the right track. I have set up Excel 2007 so that I can click on the drop-down menu in each column to filter them.

    However, in my data set there is one blank row separating two set of rows. Now, when I apply the filter on a column, all the rows above the blank row filter correctly, but all the rows below stay un-filtered.

    I guess Excel only looks at the consequtive rows, then stops when it hits a blank row. Is there any way of applying the filter beyond a blank row (i.e. the whole column)?

    Any advice is higly appreciated.

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Excel won't filter whole column.

    Best advice is don't have blank rows in your data but if you select the whole data range including the data below the blank row before you activate auto filter it should work.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Registered User
    Join Date
    02-16-2010
    Location
    Norway
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Excel won't filter whole column.

    Thanks for the reply. However, manually marking the cells in that row does not make a difference. I can even select the whole column by clicking on the column letter, but it doesn't change. What I've done now is just put an "x" in the first column in the blank row. This way the filter works for the whole data set.

    but as you said, I'm probably better off without blank rows in my spreadsheet.

  4. #4
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Excel won't filter whole column.

    Seems to work okay for me unless something's changed in 2007.

    Dom
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-11-2013
    Location
    Lannon, WI
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Excel won't filter whole column.

    Excel has always recognized a table as a set of data in rows and columns that are completely surrounded by blank cells.
    If you have a blank row of data, at least add a heading in the first column (or add a column to do so). THEN your filter or sorting tools will work.

  6. #6
    Registered User
    Join Date
    07-16-2019
    Location
    Houston
    MS-Off Ver
    2013
    Posts
    1

    Re: Excel won't filter whole column.

    I had the same issue where Excel would apply a filter to either columns A:E or F:G, but not A:G. Turns out columns F:G were set up as a table ("Design" menu appears at the top when you select any cell within those columns). There is an option within the Design menu that says "Convert to Range". After selecting that option the filter worked on all of the columns.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1