Autofilter always includes last line of data irrespective of filter

Hi Guys,

Sorry if this question has been asked/answered before but I couldn't
see it easily in a search.

I have a problem with autofilter in one particular worksheet, not all
of them.

In using autofilter via VBA I seem to get the last line of the
datatable irrespective of the criterion selected, i.e. in 613 rows of
dataI might get rows 34,45,67,98 which relate to my criterion but then
I get row 613 tagged on at the end. It happens for all my selections.

I've tried three different fixes and none worked

1) naming the exact range from row 5 (headers) column1 to row 613 col
22 as "dfilter1" and then using

Sheet12.Range("dfilter1").AutoFilter Field:=1, Criteria1:="Australia"

2) increasing the range dimensions to include rows beyond the last row
of data (i.e. lower row is now 615 not 613)

3) Including rows 5 to 65536 in my range name


I've also tried setting autoflter manually and still no joy.


I've never had this problem before - anyone else had it and sorted it?

Thanks

Peter

Next time you apply a filter to that range, notice that the dropdown arrow has
changed color to blue for that column.

And the row indicators for the range that's filtered are blue.

But that last row that shows up, but doesn't match the filter as a row indicator
of black/normal.

This means that that row is not part of the filter range.

I don't think I've ever seen this happen when applying the filter manually--but
it has happened when I applied the filter via code.

My not-so-pretty workaround....

I'd add a dummy value to the row under the last real row.
I'd apply data|filter to the new range (including that dummy row).
I'd clearcontents in that dummy cell.

Yep. If I filtered to show blanks, I'd see that extra row. (And there would
also be the (Blanks) option in the autofilter dropdown list--but I could live
with that ugliness.)

And it didn't happen in every workbook--but it seemed to happen in workbook that
was built every morning from a text file. I finally gave up and just did that
workaround.


Peter wrote:
>
> Hi Guys,
>
> Sorry if this question has been asked/answered before but I couldn't
> see it easily in a search.
>
> I have a problem with autofilter in one particular worksheet, not all
> of them.
>
> In using autofilter via VBA I seem to get the last line of the
> datatable irrespective of the criterion selected, i.e. in 613 rows of
> dataI might get rows 34,45,67,98 which relate to my criterion but then
> I get row 613 tagged on at the end. It happens for all my selections.
>
> I've tried three different fixes and none worked
>
> 1) naming the exact range from row 5 (headers) column1 to row 613 col
> 22 as "dfilter1" and then using
>
> Sheet12.Range("dfilter1").AutoFilter Field:=1, Criteria1:="Australia"
>
> 2) increasing the range dimensions to include rows beyond the last row
> of data (i.e. lower row is now 615 not 613)
>
> 3) Including rows 5 to 65536 in my range name
>
> I've also tried setting autoflter manually and still no joy.
>
> I've never had this problem before - anyone else had it and sorted it?
>
> Thanks
>
> Peter

--

Dave Peterson

Hi Dave,

Thanks for the suggestion - I've tried that and it seems to work.
Because my selections are driven by a user selecting a criterion from a
combo down box elsewhere I don't have to worry about the blanks.

I'm still puzzled as to the "why" but not so much that I intend to
ignore your workaround!

Thanks again

Peter


Dave Peterson wrote:
> Next time you apply a filter to that range, notice that the dropdown arrow has
> changed color to blue for that column.
>
> And the row indicators for the range that's filtered are blue.
>
> But that last row that shows up, but doesn't match the filter as a row indicator
> of black/normal.
>
> This means that that row is not part of the filter range.
>
> I don't think I've ever seen this happen when applying the filter manually--but
> it has happened when I applied the filter via code.
>
> My not-so-pretty workaround....
>
> I'd add a dummy value to the row under the last real row.
> I'd apply data|filter to the new range (including that dummy row).
> I'd clearcontents in that dummy cell.
>
> Yep. If I filtered to show blanks, I'd see that extra row. (And there would
> also be the (Blanks) option in the autofilter dropdown list--but I could live
> with that ugliness.)
>
> And it didn't happen in every workbook--but it seemed to happen in workbook that
> was built every morning from a text file. I finally gave up and just did that
> workaround.
>
>
> Peter wrote:
> >
> > Hi Guys,
> >
> > Sorry if this question has been asked/answered before but I couldn't
> > see it easily in a search.
> >
> > I have a problem with autofilter in one particular worksheet, not all
> > of them.
> >
> > In using autofilter via VBA I seem to get the last line of the
> > datatable irrespective of the criterion selected, i.e. in 613 rows of
> > dataI might get rows 34,45,67,98 which relate to my criterion but then
> > I get row 613 tagged on at the end. It happens for all my selections.
> >
> > I've tried three different fixes and none worked
> >
> > 1) naming the exact range from row 5 (headers) column1 to row 613 col
> > 22 as "dfilter1" and then using
> >
> > Sheet12.Range("dfilter1").AutoFilter Field:=1, Criteria1:="Australia"
> >
> > 2) increasing the range dimensions to include rows beyond the last row
> > of data (i.e. lower row is now 615 not 613)
> >
> > 3) Including rows 5 to 65536 in my range name
> >
> > I've also tried setting autoflter manually and still no joy.
> >
> > I've never had this problem before - anyone else had it and sorted it?
> >
> > Thanks
> >
> > Peter
>
> --
>
> Dave Peterson

I think the technical response to the "why" question is: "because". <vbg>

(I got nothing that'll help!)

Peter wrote:
>
> Hi Dave,
>
> Thanks for the suggestion - I've tried that and it seems to work.
> Because my selections are driven by a user selecting a criterion from a
> combo down box elsewhere I don't have to worry about the blanks.
>
> I'm still puzzled as to the "why" but not so much that I intend to
> ignore your workaround!
>
> Thanks again
>
> Peter
>
> Dave Peterson wrote:
> > Next time you apply a filter to that range, notice that the dropdown arrow has
> > changed color to blue for that column.
> >
> > And the row indicators for the range that's filtered are blue.
> >
> > But that last row that shows up, but doesn't match the filter as a row indicator
> > of black/normal.
> >
> > This means that that row is not part of the filter range.
> >
> > I don't think I've ever seen this happen when applying the filter manually--but
> > it has happened when I applied the filter via code.
> >
> > My not-so-pretty workaround....
> >
> > I'd add a dummy value to the row under the last real row.
> > I'd apply data|filter to the new range (including that dummy row).
> > I'd clearcontents in that dummy cell.
> >
> > Yep. If I filtered to show blanks, I'd see that extra row. (And there would
> > also be the (Blanks) option in the autofilter dropdown list--but I could live
> > with that ugliness.)
> >
> > And it didn't happen in every workbook--but it seemed to happen in workbook that
> > was built every morning from a text file. I finally gave up and just did that
> > workaround.
> >
> >
> > Peter wrote:
> > >
> > > Hi Guys,
> > >
> > > Sorry if this question has been asked/answered before but I couldn't
> > > see it easily in a search.
> > >
> > > I have a problem with autofilter in one particular worksheet, not all
> > > of them.
> > >
> > > In using autofilter via VBA I seem to get the last line of the
> > > datatable irrespective of the criterion selected, i.e. in 613 rows of
> > > dataI might get rows 34,45,67,98 which relate to my criterion but then
> > > I get row 613 tagged on at the end. It happens for all my selections.
> > >
> > > I've tried three different fixes and none worked
> > >
> > > 1) naming the exact range from row 5 (headers) column1 to row 613 col
> > > 22 as "dfilter1" and then using
> > >
> > > Sheet12.Range("dfilter1").AutoFilter Field:=1, Criteria1:="Australia"
> > >
> > > 2) increasing the range dimensions to include rows beyond the last row
> > > of data (i.e. lower row is now 615 not 613)
> > >
> > > 3) Including rows 5 to 65536 in my range name
> > >
> > > I've also tried setting autoflter manually and still no joy.
> > >
> > > I've never had this problem before - anyone else had it and sorted it?
> > >
> > > Thanks
> > >
> > > Peter
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Also, if you have a Subtotal formula in the last row of the table, that
row is excluded from the filter, and always remains visible.


Dave Peterson wrote:
> I think the technical response to the "why" question is: "because". <vbg>
>
> (I got nothing that'll help!)
>
> Peter wrote:
>
>>Hi Dave,
>>
>>Thanks for the suggestion - I've tried that and it seems to work.
>>Because my selections are driven by a user selecting a criterion from a
>>combo down box elsewhere I don't have to worry about the blanks.
>>
>>I'm still puzzled as to the "why" but not so much that I intend to
>>ignore your workaround!
>>
>>Thanks again
>>
>>Peter
>>
>>Dave Peterson wrote:
>>
>>>Next time you apply a filter to that range, notice that the dropdown arrow has
>>>changed color to blue for that column.
>>>
>>>And the row indicators for the range that's filtered are blue.
>>>
>>>But that last row that shows up, but doesn't match the filter as a row indicator
>>>of black/normal.
>>>
>>>This means that that row is not part of the filter range.
>>>
>>>I don't think I've ever seen this happen when applying the filter manually--but
>>>it has happened when I applied the filter via code.
>>>
>>>My not-so-pretty workaround....
>>>
>>>I'd add a dummy value to the row under the last real row.
>>>I'd apply data|filter to the new range (including that dummy row).
>>>I'd clearcontents in that dummy cell.
>>>
>>>Yep. If I filtered to show blanks, I'd see that extra row. (And there would
>>>also be the (Blanks) option in the autofilter dropdown list--but I could live
>>>with that ugliness.)
>>>
>>>And it didn't happen in every workbook--but it seemed to happen in workbook that
>>>was built every morning from a text file. I finally gave up and just did that
>>>workaround.
>>>
>>>
>>>Peter wrote:
>>>
>>>>Hi Guys,
>>>>
>>>>Sorry if this question has been asked/answered before but I couldn't
>>>>see it easily in a search.
>>>>
>>>>I have a problem with autofilter in one particular worksheet, not all
>>>>of them.
>>>>
>>>>In using autofilter via VBA I seem to get the last line of the
>>>>datatable irrespective of the criterion selected, i.e. in 613 rows of
>>>>dataI might get rows 34,45,67,98 which relate to my criterion but then
>>>>I get row 613 tagged on at the end. It happens for all my selections.
>>>>
>>>>I've tried three different fixes and none worked
>>>>
>>>>1) naming the exact range from row 5 (headers) column1 to row 613 col
>>>>22 as "dfilter1" and then using
>>>>
>>>>Sheet12.Range("dfilter1").AutoFilter Field:=1, Criteria1:="Australia"
>>>>
>>>>2) increasing the range dimensions to include rows beyond the last row
>>>>of data (i.e. lower row is now 615 not 613)
>>>>
>>>>3) Including rows 5 to 65536 in my range name
>>>>
>>>>I've also tried setting autoflter manually and still no joy.
>>>>
>>>>I've never had this problem before - anyone else had it and sorted it?
>>>>
>>>>Thanks
>>>>
>>>>Peter
>>>
>>>--
>>>
>>>Dave Peterson
>>
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Unless I do something to include that row in the filtered range, right?

But I do have to do something weird--like add something in the cell under the
"Grand total" row, apply the filter and clean up that cell.

Debra Dalgleish wrote:
>
> Also, if you have a Subtotal formula in the last row of the table, that
> row is excluded from the filter, and always remains visible.
>
> Dave Peterson wrote:
> > I think the technical response to the "why" question is: "because". <vbg>
> >
> > (I got nothing that'll help!)
> >
> > Peter wrote:
> >
> >>Hi Dave,
> >>
> >>Thanks for the suggestion - I've tried that and it seems to work.
> >>Because my selections are driven by a user selecting a criterion from a
> >>combo down box elsewhere I don't have to worry about the blanks.
> >>
> >>I'm still puzzled as to the "why" but not so much that I intend to
> >>ignore your workaround!
> >>
> >>Thanks again
> >>
> >>Peter
> >>
> >>Dave Peterson wrote:
> >>
> >>>Next time you apply a filter to that range, notice that the dropdown arrow has
> >>>changed color to blue for that column.
> >>>
> >>>And the row indicators for the range that's filtered are blue.
> >>>
> >>>But that last row that shows up, but doesn't match the filter as a row indicator
> >>>of black/normal.
> >>>
> >>>This means that that row is not part of the filter range.
> >>>
> >>>I don't think I've ever seen this happen when applying the filter manually--but
> >>>it has happened when I applied the filter via code.
> >>>
> >>>My not-so-pretty workaround....
> >>>
> >>>I'd add a dummy value to the row under the last real row.
> >>>I'd apply data|filter to the new range (including that dummy row).
> >>>I'd clearcontents in that dummy cell.
> >>>
> >>>Yep. If I filtered to show blanks, I'd see that extra row. (And there would
> >>>also be the (Blanks) option in the autofilter dropdown list--but I could live
> >>>with that ugliness.)
> >>>
> >>>And it didn't happen in every workbook--but it seemed to happen in workbook that
> >>>was built every morning from a text file. I finally gave up and just did that
> >>>workaround.
> >>>
> >>>
> >>>Peter wrote:
> >>>
> >>>>Hi Guys,
> >>>>
> >>>>Sorry if this question has been asked/answered before but I couldn't
> >>>>see it easily in a search.
> >>>>
> >>>>I have a problem with autofilter in one particular worksheet, not all
> >>>>of them.
> >>>>
> >>>>In using autofilter via VBA I seem to get the last line of the
> >>>>datatable irrespective of the criterion selected, i.e. in 613 rows of
> >>>>dataI might get rows 34,45,67,98 which relate to my criterion but then
> >>>>I get row 613 tagged on at the end. It happens for all my selections.
> >>>>
> >>>>I've tried three different fixes and none worked
> >>>>
> >>>>1) naming the exact range from row 5 (headers) column1 to row 613 col
> >>>>22 as "dfilter1" and then using
> >>>>
> >>>>Sheet12.Range("dfilter1").AutoFilter Field:=1, Criteria1:="Australia"
> >>>>
> >>>>2) increasing the range dimensions to include rows beyond the last row
> >>>>of data (i.e. lower row is now 615 not 613)
> >>>>
> >>>>3) Including rows 5 to 65536 in my range name
> >>>>
> >>>>I've also tried setting autoflter manually and still no joy.
> >>>>
> >>>>I've never had this problem before - anyone else had it and sorted it?
> >>>>
> >>>>Thanks
> >>>>
> >>>>Peter
> >>>
> >>>--
> >>>
> >>>Dave Peterson
> >>
> >
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html

--

Dave Peterson

Using Excel 2003, I include the subtotal row when selecting the range
for the autofilter, and there's nothing in the following row. That last
row is always shown, and has a black row number, instead of blue.

Dave Peterson wrote:
> Unless I do something to include that row in the filtered range, right?
>
> But I do have to do something weird--like add something in the cell under the
> "Grand total" row, apply the filter and clean up that cell.
>
> Debra Dalgleish wrote:
>
>>Also, if you have a Subtotal formula in the last row of the table, that
>>row is excluded from the filter, and always remains visible.
>>
>>Dave Peterson wrote:
>>
>>>I think the technical response to the "why" question is: "because". <vbg>
>>>
>>>(I got nothing that'll help!)
>>>
>>>Peter wrote:
>>>
>>>
>>>>Hi Dave,
>>>>
>>>>Thanks for the suggestion - I've tried that and it seems to work.
>>>>Because my selections are driven by a user selecting a criterion from a
>>>>combo down box elsewhere I don't have to worry about the blanks.
>>>>
>>>>I'm still puzzled as to the "why" but not so much that I intend to
>>>>ignore your workaround!
>>>>
>>>>Thanks again
>>>>
>>>>Peter
>>>>
>>>>Dave Peterson wrote:
>>>>
>>>>
>>>>>Next time you apply a filter to that range, notice that the dropdown arrow has
>>>>>changed color to blue for that column.
>>>>>
>>>>>And the row indicators for the range that's filtered are blue.
>>>>>
>>>>>But that last row that shows up, but doesn't match the filter as a row indicator
>>>>>of black/normal.
>>>>>
>>>>>This means that that row is not part of the filter range.
>>>>>
>>>>>I don't think I've ever seen this happen when applying the filter manually--but
>>>>>it has happened when I applied the filter via code.
>>>>>
>>>>>My not-so-pretty workaround....
>>>>>
>>>>>I'd add a dummy value to the row under the last real row.
>>>>>I'd apply data|filter to the new range (including that dummy row).
>>>>>I'd clearcontents in that dummy cell.
>>>>>
>>>>>Yep. If I filtered to show blanks, I'd see that extra row. (And there would
>>>>>also be the (Blanks) option in the autofilter dropdown list--but I could live
>>>>>with that ugliness.)
>>>>>
>>>>>And it didn't happen in every workbook--but it seemed to happen in workbook that
>>>>>was built every morning from a text file. I finally gave up and just did that
>>>>>workaround.
>>>>>
>>>>>
>>>>>Peter wrote:
>>>>>
>>>>>
>>>>>>Hi Guys,
>>>>>>
>>>>>>Sorry if this question has been asked/answered before but I couldn't
>>>>>>see it easily in a search.
>>>>>>
>>>>>>I have a problem with autofilter in one particular worksheet, not all
>>>>>>of them.
>>>>>>
>>>>>>In using autofilter via VBA I seem to get the last line of the
>>>>>>datatable irrespective of the criterion selected, i.e. in 613 rows of
>>>>>>dataI might get rows 34,45,67,98 which relate to my criterion but then
>>>>>>I get row 613 tagged on at the end. It happens for all my selections.
>>>>>>
>>>>>>I've tried three different fixes and none worked
>>>>>>
>>>>>>1) naming the exact range from row 5 (headers) column1 to row 613 col
>>>>>>22 as "dfilter1" and then using
>>>>>>
>>>>>>Sheet12.Range("dfilter1").AutoFilter Field:=1, Criteria1:="Australia"
>>>>>>
>>>>>>2) increasing the range dimensions to include rows beyond the last row
>>>>>>of data (i.e. lower row is now 615 not 613)
>>>>>>
>>>>>>3) Including rows 5 to 65536 in my range name
>>>>>>
>>>>>>I've also tried setting autoflter manually and still no joy.
>>>>>>
>>>>>>I've never had this problem before - anyone else had it and sorted it?
>>>>>>
>>>>>>Thanks
>>>>>>
>>>>>>Peter
>>>>>
>>>>>--
>>>>>
>>>>>Dave Peterson
>>>>
>>--
>>Debra Dalgleish
>>Contextures
>>http://www.contextures.com/tiptech.html
>
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

I had to add something under that "grand total" row. Then I applied the filter
(including that "special" row).

Then I cleared the contents of the cell.

When I did that, the "grand total" row was included in the autofilter range.

But I may have said that <gd&r...vvf>



Debra Dalgleish wrote:
>
> Using Excel 2003, I include the subtotal row when selecting the range
> for the autofilter, and there's nothing in the following row. That last
> row is always shown, and has a black row number, instead of blue.
>
> Dave Peterson wrote:
> > Unless I do something to include that row in the filtered range, right?
> >
> > But I do have to do something weird--like add something in the cell under the
> > "Grand total" row, apply the filter and clean up that cell.
> >
> > Debra Dalgleish wrote:
> >
> >>Also, if you have a Subtotal formula in the last row of the table, that
> >>row is excluded from the filter, and always remains visible.
> >>
> >>Dave Peterson wrote:
> >>
> >>>I think the technical response to the "why" question is: "because". <vbg>
> >>>
> >>>(I got nothing that'll help!)
> >>>
> >>>Peter wrote:
> >>>
> >>>
> >>>>Hi Dave,
> >>>>
> >>>>Thanks for the suggestion - I've tried that and it seems to work.
> >>>>Because my selections are driven by a user selecting a criterion from a
> >>>>combo down box elsewhere I don't have to worry about the blanks.
> >>>>
> >>>>I'm still puzzled as to the "why" but not so much that I intend to
> >>>>ignore your workaround!
> >>>>
> >>>>Thanks again
> >>>>
> >>>>Peter
> >>>>
> >>>>Dave Peterson wrote:
> >>>>
> >>>>
> >>>>>Next time you apply a filter to that range, notice that the dropdown arrow has
> >>>>>changed color to blue for that column.
> >>>>>
> >>>>>And the row indicators for the range that's filtered are blue.
> >>>>>
> >>>>>But that last row that shows up, but doesn't match the filter as a row indicator
> >>>>>of black/normal.
> >>>>>
> >>>>>This means that that row is not part of the filter range.
> >>>>>
> >>>>>I don't think I've ever seen this happen when applying the filter manually--but
> >>>>>it has happened when I applied the filter via code.
> >>>>>
> >>>>>My not-so-pretty workaround....
> >>>>>
> >>>>>I'd add a dummy value to the row under the last real row.
> >>>>>I'd apply data|filter to the new range (including that dummy row).
> >>>>>I'd clearcontents in that dummy cell.
> >>>>>
> >>>>>Yep. If I filtered to show blanks, I'd see that extra row. (And there would
> >>>>>also be the (Blanks) option in the autofilter dropdown list--but I could live
> >>>>>with that ugliness.)
> >>>>>
> >>>>>And it didn't happen in every workbook--but it seemed to happen in workbook that
> >>>>>was built every morning from a text file. I finally gave up and just did that
> >>>>>workaround.
> >>>>>
> >>>>>
> >>>>>Peter wrote:
> >>>>>
> >>>>>
> >>>>>>Hi Guys,
> >>>>>>
> >>>>>>Sorry if this question has been asked/answered before but I couldn't
> >>>>>>see it easily in a search.
> >>>>>>
> >>>>>>I have a problem with autofilter in one particular worksheet, not all
> >>>>>>of them.
> >>>>>>
> >>>>>>In using autofilter via VBA I seem to get the last line of the
> >>>>>>datatable irrespective of the criterion selected, i.e. in 613 rows of
> >>>>>>dataI might get rows 34,45,67,98 which relate to my criterion but then
> >>>>>>I get row 613 tagged on at the end. It happens for all my selections.
> >>>>>>
> >>>>>>I've tried three different fixes and none worked
> >>>>>>
> >>>>>>1) naming the exact range from row 5 (headers) column1 to row 613 col
> >>>>>>22 as "dfilter1" and then using
> >>>>>>
> >>>>>>Sheet12.Range("dfilter1").AutoFilter Field:=1, Criteria1:="Australia"
> >>>>>>
> >>>>>>2) increasing the range dimensions to include rows beyond the last row
> >>>>>>of data (i.e. lower row is now 615 not 613)
> >>>>>>
> >>>>>>3) Including rows 5 to 65536 in my range name
> >>>>>>
> >>>>>>I've also tried setting autoflter manually and still no joy.
> >>>>>>
> >>>>>>I've never had this problem before - anyone else had it and sorted it?
> >>>>>>
> >>>>>>Thanks
> >>>>>>
> >>>>>>Peter
> >>>>>
> >>>>>--
> >>>>>
> >>>>>Dave Peterson
> >>>>
> >>--
> >>Debra Dalgleish
> >>Contextures
> >>http://www.contextures.com/tiptech.html
> >
> >
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html

--

Dave Peterson

Ahhhh. Now I see what you mean.
Yhy didn't you say that the first time? <g>

Dave Peterson wrote:
> I had to add something under that "grand total" row. Then I applied the filter
> (including that "special" row).
>
> Then I cleared the contents of the cell.
>
> When I did that, the "grand total" row was included in the autofilter range.
>
> But I may have said that <gd&r...vvf>
>
>
>
> Debra Dalgleish wrote:
>
>>Using Excel 2003, I include the subtotal row when selecting the range
>>for the autofilter, and there's nothing in the following row. That last
>>row is always shown, and has a black row number, instead of blue.
>>
>>Dave Peterson wrote:
>>
>>>Unless I do something to include that row in the filtered range, right?
>>>
>>>But I do have to do something weird--like add something in the cell under the
>>>"Grand total" row, apply the filter and clean up that cell.
>>>
>>>Debra Dalgleish wrote:
>>>
>>>
>>>>Also, if you have a Subtotal formula in the last row of the table, that
>>>>row is excluded from the filter, and always remains visible.
>>>>
>>>>Dave Peterson wrote:
>>>>
>>>>
>>>>>I think the technical response to the "why" question is: "because". <vbg>
>>>>>
>>>>>(I got nothing that'll help!)
>>>>>
>>>>>Peter wrote:
>>>>>
>>>>>
>>>>>
>>>>>>Hi Dave,
>>>>>>
>>>>>>Thanks for the suggestion - I've tried that and it seems to work.
>>>>>>Because my selections are driven by a user selecting a criterion from a
>>>>>>combo down box elsewhere I don't have to worry about the blanks.
>>>>>>
>>>>>>I'm still puzzled as to the "why" but not so much that I intend to
>>>>>>ignore your workaround!
>>>>>>
>>>>>>Thanks again
>>>>>>
>>>>>>Peter
>>>>>>
>>>>>>Dave Peterson wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Next time you apply a filter to that range, notice that the dropdown arrow has
>>>>>>>changed color to blue for that column.
>>>>>>>
>>>>>>>And the row indicators for the range that's filtered are blue.
>>>>>>>
>>>>>>>But that last row that shows up, but doesn't match the filter as a row indicator
>>>>>>>of black/normal.
>>>>>>>
>>>>>>>This means that that row is not part of the filter range.
>>>>>>>
>>>>>>>I don't think I've ever seen this happen when applying the filter manually--but
>>>>>>>it has happened when I applied the filter via code.
>>>>>>>
>>>>>>>My not-so-pretty workaround....
>>>>>>>
>>>>>>>I'd add a dummy value to the row under the last real row.
>>>>>>>I'd apply data|filter to the new range (including that dummy row).
>>>>>>>I'd clearcontents in that dummy cell.
>>>>>>>
>>>>>>>Yep. If I filtered to show blanks, I'd see that extra row. (And there would
>>>>>>>also be the (Blanks) option in the autofilter dropdown list--but I could live
>>>>>>>with that ugliness.)
>>>>>>>
>>>>>>>And it didn't happen in every workbook--but it seemed to happen in workbook that
>>>>>>>was built every morning from a text file. I finally gave up and just did that
>>>>>>>workaround.
>>>>>>>
>>>>>>>
>>>>>>>Peter wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>Hi Guys,
>>>>>>>>
>>>>>>>>Sorry if this question has been asked/answered before but I couldn't
>>>>>>>>see it easily in a search.
>>>>>>>>
>>>>>>>>I have a problem with autofilter in one particular worksheet, not all
>>>>>>>>of them.
>>>>>>>>
>>>>>>>>In using autofilter via VBA I seem to get the last line of the
>>>>>>>>datatable irrespective of the criterion selected, i.e. in 613 rows of
>>>>>>>>dataI might get rows 34,45,67,98 which relate to my criterion but then
>>>>>>>>I get row 613 tagged on at the end. It happens for all my selections.
>>>>>>>>
>>>>>>>>I've tried three different fixes and none worked
>>>>>>>>
>>>>>>>>1) naming the exact range from row 5 (headers) column1 to row 613 col
>>>>>>>>22 as "dfilter1" and then using
>>>>>>>>
>>>>>>>>Sheet12.Range("dfilter1").AutoFilter Field:=1, Criteria1:="Australia"
>>>>>>>>
>>>>>>>>2) increasing the range dimensions to include rows beyond the last row
>>>>>>>>of data (i.e. lower row is now 615 not 613)
>>>>>>>>
>>>>>>>>3) Including rows 5 to 65536 in my range name
>>>>>>>>
>>>>>>>>I've also tried setting autoflter manually and still no joy.
>>>>>>>>
>>>>>>>>I've never had this problem before - anyone else had it and sorted it?
>>>>>>>>
>>>>>>>>Thanks
>>>>>>>>
>>>>>>>>Peter
>>>>>>>
>>>>>>>--
>>>>>>>
>>>>>>>Dave Peterson
>>>>>>
>>>>--
>>>>Debra Dalgleish
>>>>Contextures
>>>>http://www.contextures.com/tiptech.html
>>>
>>>
>>--
>>Debra Dalgleish
>>Contextures
>>http://www.contextures.com/tiptech.html
>
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

I should have. My mistake, ma'am. Never happen again. Please forgive me.

<vbg>

Debra Dalgleish wrote:
>
> Ahhhh. Now I see what you mean.
> Yhy didn't you say that the first time? <g>
>
> Dave Peterson wrote:
> > I had to add something under that "grand total" row. Then I applied the filter
> > (including that "special" row).
> >
> > Then I cleared the contents of the cell.
> >
> > When I did that, the "grand total" row was included in the autofilter range.
> >
> > But I may have said that <gd&r...vvf>
> >
> >
> >
> > Debra Dalgleish wrote:
> >
> >>Using Excel 2003, I include the subtotal row when selecting the range
> >>for the autofilter, and there's nothing in the following row. That last
> >>row is always shown, and has a black row number, instead of blue.
> >>
> >>Dave Peterson wrote:
> >>
> >>>Unless I do something to include that row in the filtered range, right?
> >>>
> >>>But I do have to do something weird--like add something in the cell under the
> >>>"Grand total" row, apply the filter and clean up that cell.
> >>>
> >>>Debra Dalgleish wrote:
> >>>
> >>>
> >>>>Also, if you have a Subtotal formula in the last row of the table, that
> >>>>row is excluded from the filter, and always remains visible.
> >>>>
> >>>>Dave Peterson wrote:
> >>>>
> >>>>
> >>>>>I think the technical response to the "why" question is: "because". <vbg>
> >>>>>
> >>>>>(I got nothing that'll help!)
> >>>>>
> >>>>>Peter wrote:
> >>>>>
> >>>>>
> >>>>>
> >>>>>>Hi Dave,
> >>>>>>
> >>>>>>Thanks for the suggestion - I've tried that and it seems to work.
> >>>>>>Because my selections are driven by a user selecting a criterion from a
> >>>>>>combo down box elsewhere I don't have to worry about the blanks.
> >>>>>>
> >>>>>>I'm still puzzled as to the "why" but not so much that I intend to
> >>>>>>ignore your workaround!
> >>>>>>
> >>>>>>Thanks again
> >>>>>>
> >>>>>>Peter
> >>>>>>
> >>>>>>Dave Peterson wrote:
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>>Next time you apply a filter to that range, notice that the dropdown arrow has
> >>>>>>>changed color to blue for that column.
> >>>>>>>
> >>>>>>>And the row indicators for the range that's filtered are blue.
> >>>>>>>
> >>>>>>>But that last row that shows up, but doesn't match the filter as a row indicator
> >>>>>>>of black/normal.
> >>>>>>>
> >>>>>>>This means that that row is not part of the filter range.
> >>>>>>>
> >>>>>>>I don't think I've ever seen this happen when applying the filter manually--but
> >>>>>>>it has happened when I applied the filter via code.
> >>>>>>>
> >>>>>>>My not-so-pretty workaround....
> >>>>>>>
> >>>>>>>I'd add a dummy value to the row under the last real row.
> >>>>>>>I'd apply data|filter to the new range (including that dummy row).
> >>>>>>>I'd clearcontents in that dummy cell.
> >>>>>>>
> >>>>>>>Yep. If I filtered to show blanks, I'd see that extra row. (And there would
> >>>>>>>also be the (Blanks) option in the autofilter dropdown list--but I could live
> >>>>>>>with that ugliness.)
> >>>>>>>
> >>>>>>>And it didn't happen in every workbook--but it seemed to happen in workbook that
> >>>>>>>was built every morning from a text file. I finally gave up and just did that
> >>>>>>>workaround.
> >>>>>>>
> >>>>>>>
> >>>>>>>Peter wrote:
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>>Hi Guys,
> >>>>>>>>
> >>>>>>>>Sorry if this question has been asked/answered before but I couldn't
> >>>>>>>>see it easily in a search.
> >>>>>>>>
> >>>>>>>>I have a problem with autofilter in one particular worksheet, not all
> >>>>>>>>of them.
> >>>>>>>>
> >>>>>>>>In using autofilter via VBA I seem to get the last line of the
> >>>>>>>>datatable irrespective of the criterion selected, i.e. in 613 rows of
> >>>>>>>>dataI might get rows 34,45,67,98 which relate to my criterion but then
> >>>>>>>>I get row 613 tagged on at the end. It happens for all my selections.
> >>>>>>>>
> >>>>>>>>I've tried three different fixes and none worked
> >>>>>>>>
> >>>>>>>>1) naming the exact range from row 5 (headers) column1 to row 613 col
> >>>>>>>>22 as "dfilter1" and then using
> >>>>>>>>
> >>>>>>>>Sheet12.Range("dfilter1").AutoFilter Field:=1, Criteria1:="Australia"
> >>>>>>>>
> >>>>>>>>2) increasing the range dimensions to include rows beyond the last row
> >>>>>>>>of data (i.e. lower row is now 615 not 613)
> >>>>>>>>
> >>>>>>>>3) Including rows 5 to 65536 in my range name
> >>>>>>>>
> >>>>>>>>I've also tried setting autoflter manually and still no joy.
> >>>>>>>>
> >>>>>>>>I've never had this problem before - anyone else had it and sorted it?
> >>>>>>>>
> >>>>>>>>Thanks
> >>>>>>>>
> >>>>>>>>Peter
> >>>>>>>
> >>>>>>>--
> >>>>>>>
> >>>>>>>Dave Peterson
> >>>>>>
> >>>>--
> >>>>Debra Dalgleish
> >>>>Contextures
> >>>>http://www.contextures.com/tiptech.html
> >>>
> >>>
> >>--
> >>Debra Dalgleish
> >>Contextures
> >>http://www.contextures.com/tiptech.html
> >
> >
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html

--

Dave Peterson