Autofilter misses last line Richard 29.09.2006 16:17:01 I have a 2075 row database that I autofilter by technical area (i.e. Data/Filter/Autofilter on technical area column header). The filter works fine, except no matter what technical area I select from autofilter pulldown, the last row always displays. I have double checked that there are no empty cells in the last row. I have also verified that there are no empty cells in the last row by writing a short macro that starts on column A of the last row, then xltoRight (i.e. Range("a2075", Range("a2075").End(xlToRight)).Select does indeed select all the cells in the data set) Any ideas why the last row doesn't my autofilter apply to the last row? -- Richard

Re: Autofilter misses last line Dave Peterson <petersod[ at ]verizonXSPAM.net> 29.09.2006 16:19:44 Excel tries to help by using colors. If you filter a field, then that arrow will turn blue. Your row numbers in that filtered range will turn blue if the data is filtered. My bet is that last row number is still black. That means that this row isn't part of the filtered range. I'd remove the filter and select the whole range and reapply the filter. Richard wrote: [Quoted Text] > > I have a 2075 row database that I autofilter by technical area > (i.e. Data/Filter/Autofilter on technical area column header). > > The filter works fine, except no matter what technical area I select from > autofilter pulldown, the last row always displays. > > I have double checked that there are no empty cells in the last row. > I have also verified that there are no empty cells in the last row by > writing a short macro that starts on column A of the last row, then xltoRight > (i.e. Range("a2075", Range("a2075").End(xlToRight)).Select does indeed > select all the cells in the data set) > > Any ideas why the last row doesn't my autofilter apply to the last row? > -- > Richard -- Dave Peterson

Re: Autofilter misses last line Richard 29.09.2006 16:37:02 You're right that the 2075th row is not blue. However, even if I hand-select that data base (including row 2075) before selecting Autofilter, it still keeps row 2075 no matter what technical area I select. -- Richard "Dave Peterson" wrote: [Quoted Text] > Excel tries to help by using colors. > > If you filter a field, then that arrow will turn blue. > > Your row numbers in that filtered range will turn blue if the data is filtered. > > My bet is that last row number is still black. > > That means that this row isn't part of the filtered range. > > I'd remove the filter and select the whole range and reapply the filter. > > > > Richard wrote: > > > > I have a 2075 row database that I autofilter by technical area > > (i.e. Data/Filter/Autofilter on technical area column header). > > > > The filter works fine, except no matter what technical area I select from > > autofilter pulldown, the last row always displays. > > > > I have double checked that there are no empty cells in the last row. > > I have also verified that there are no empty cells in the last row by > > writing a short macro that starts on column A of the last row, then xltoRight > > (i.e. Range("a2075", Range("a2075").End(xlToRight)).Select does indeed > > select all the cells in the data set) > > > > Any ideas why the last row doesn't my autofilter apply to the last row? > > -- > > Richard > > -- > > Dave Peterson >

Re: Autofilter misses last line "Bill Ridgeway" <info[ at ]1001solutions.co.uk> 29.09.2006 16:45:42 Richard, I have also seen a similar problem where the spreadsheet will sort all bar the last few lines. If you insert data within the spreadsheet it will be included with anything like sorting. However, data added to the end is not always included. I've got round it by changing the print area (Click on <Insert><Name><Define><Print_Area>). I've not had this problem too many time so I haven't been able to study it so I'm not sure what causes the problem or why that should work and there may be a better way out of it. Hope this helps. Bill Ridgeway Computer Solutions "Richard" <Richard[ at ]discussions.microsoft.com> wrote in message news:B492C582-BBAF-4A5B-91AC-92CE3E053243[ at ]microsoft.com... [Quoted Text] >I have a 2075 row database that I autofilter by technical area > (i.e. Data/Filter/Autofilter on technical area column header). > > The filter works fine, except no matter what technical area I select from > autofilter pulldown, the last row always displays. > > I have double checked that there are no empty cells in the last row. > I have also verified that there are no empty cells in the last row by > writing a short macro that starts on column A of the last row, then > xltoRight > (i.e. Range("a2075", Range("a2075").End(xlToRight)).Select does indeed > select all the cells in the data set) > > Any ideas why the last row doesn't my autofilter apply to the last row? > -- > Richard

Re: Autofilter misses last line Richard 29.09.2006 17:03:02 Bill, Adjusting the print area didn't help. However, when I selected all the data plus one blank row, it did include the 'real' last row (2075) in the filtering. Only problem is that it now as a filter option listed as 'blanks' - which is not too bad of a problem. -- Richard "Bill Ridgeway" wrote: [Quoted Text] > Richard, > > I have also seen a similar problem where the spreadsheet will sort all bar > the last few lines. If you insert data within the spreadsheet it will be > included with anything like sorting. However, data added to the end is not > always included. I've got round it by changing the print area (Click on > <Insert><Name><Define><Print_Area>). I've not had this problem too many > time so I haven't been able to study it so I'm not sure what causes the > problem or why that should work and there may be a better way out of it. > > Hope this helps. > > Bill Ridgeway > Computer Solutions > > "Richard" <Richard[ at ]discussions.microsoft.com> wrote in message > news:B492C582-BBAF-4A5B-91AC-92CE3E053243[ at ]microsoft.com... > >I have a 2075 row database that I autofilter by technical area > > (i.e. Data/Filter/Autofilter on technical area column header). > > > > The filter works fine, except no matter what technical area I select from > > autofilter pulldown, the last row always displays. > > > > I have double checked that there are no empty cells in the last row. > > I have also verified that there are no empty cells in the last row by > > writing a short macro that starts on column A of the last row, then > > xltoRight > > (i.e. Range("a2075", Range("a2075").End(xlToRight)).Select does indeed > > select all the cells in the data set) > > > > Any ideas why the last row doesn't my autofilter apply to the last row? > > -- > > Richard > > >

Re: Autofilter misses last line "Pete_UK" <pashurst[ at ]auditel.net> 29.09.2006 18:20:08 Richard, having done that, you could now highlight row 2076 (the blank row) and Edit | Delete to remove it. That should reset your filter range so that you do not get "Blanks" listed in the drop-down. I suspect that you might have two cells somewhere that are merged together - Excel thinks that there are only 2074 cells (and therefore rows) in the filter range. Hope this helps. Pete Richard wrote: [Quoted Text] > Bill, > Adjusting the print area didn't help. > However, when I selected all the data plus one blank row, it did include the > 'real' last row (2075) in the filtering. Only problem is that it now as a > filter option listed as 'blanks' - which is not too bad of a problem. > -- > Richard > > > "Bill Ridgeway" wrote: > > > Richard, > > > > I have also seen a similar problem where the spreadsheet will sort all bar > > the last few lines. If you insert data within the spreadsheet it will be > > included with anything like sorting. However, data added to the end is not > > always included. I've got round it by changing the print area (Click on > > <Insert><Name><Define><Print_Area>). I've not had this problem too many > > time so I haven't been able to study it so I'm not sure what causes the > > problem or why that should work and there may be a better way out of it. > > > > Hope this helps. > > > > Bill Ridgeway > > Computer Solutions > > > > "Richard" <Richard[ at ]discussions.microsoft.com> wrote in message > > news:B492C582-BBAF-4A5B-91AC-92CE3E053243[ at ]microsoft.com... > > >I have a 2075 row database that I autofilter by technical area > > > (i.e. Data/Filter/Autofilter on technical area column header). > > > > > > The filter works fine, except no matter what technical area I select from > > > autofilter pulldown, the last row always displays. > > > > > > I have double checked that there are no empty cells in the last row. > > > I have also verified that there are no empty cells in the last row by > > > writing a short macro that starts on column A of the last row, then > > > xltoRight > > > (i.e. Range("a2075", Range("a2075").End(xlToRight)).Select does indeed > > > select all the cells in the data set) > > > > > > Any ideas why the last row doesn't my autofilter apply to the last row? > > > -- > > > Richard > > > > > >

Re: Autofilter misses last line Dave Peterson <petersod[ at ]verizonXSPAM.net> 29.09.2006 19:48:13 I've only seen a couple of worksheets that had this problem--that the last row wouldn't be included in the autofilter range. My workaround was to add something to a cell under that row. Select the whole range--including that dummy row and apply data|filter|autofilter to that extended range. Then I'd clear the contents of that dummy cell. Yes, I usually ended up with an extra blank line in my autofilter range. But I could live with that. Richard wrote: [Quoted Text] > > You're right that the 2075th row is not blue. > However, even if I hand-select that data base (including row 2075) before > selecting Autofilter, it still keeps row 2075 no matter what technical area I > select. > -- > Richard > > "Dave Peterson" wrote: > > > Excel tries to help by using colors. > > > > If you filter a field, then that arrow will turn blue. > > > > Your row numbers in that filtered range will turn blue if the data is filtered. > > > > My bet is that last row number is still black. > > > > That means that this row isn't part of the filtered range. > > > > I'd remove the filter and select the whole range and reapply the filter. > > > > > > > > Richard wrote: > > > > > > I have a 2075 row database that I autofilter by technical area > > > (i.e. Data/Filter/Autofilter on technical area column header). > > > > > > The filter works fine, except no matter what technical area I select from > > > autofilter pulldown, the last row always displays. > > > > > > I have double checked that there are no empty cells in the last row. > > > I have also verified that there are no empty cells in the last row by > > > writing a short macro that starts on column A of the last row, then xltoRight > > > (i.e. Range("a2075", Range("a2075").End(xlToRight)).Select does indeed > > > select all the cells in the data set) > > > > > > Any ideas why the last row doesn't my autofilter apply to the last row? > > > -- > > > Richard > > > > -- > > > > Dave Peterson > > -- Dave Peterson

Re: Autofilter misses last line Debra Dalgleish <dsd[ at ]contexturesXSPAM.com> 30.09.2006 00:37:03 When the last line of data isn't included in the filtered range, it's often because the row contains a SUBTOTAL formula. Richard wrote: [Quoted Text] > Bill, > Adjusting the print area didn't help. > However, when I selected all the data plus one blank row, it did include the > 'real' last row (2075) in the filtering. Only problem is that it now as a > filter option listed as 'blanks' - which is not too bad of a problem. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html