Problem: last row not filtered

[dave61]

I have a spreadsheet, with a list of records approx 350 rows x 75
columns, on which I regularly use autofilter on several columns. It has
been in use for about three years, initially without problems but
recently I have found that last row is never filtered out, regardless
of the filter criteria used. The row immediately after is blank, if I
use the select current area button (or macro command) the last row is
included. The spreadsheet was developed and is usually run on Excel 97
but it also occurs under Excel 2003. It is regularly run by myself and
several colleagues, on various machines, all under Excel 97; the
problem occurs every time.

Any ideas?

 

[Dave Peterson]

When you apply a filter to show/hide rows, you'll notice that the row numbers in
the filter range change to blue text.

When you look at that last row, is its row number blue or grey?

If it's grey, then that row isn't included in the autofilter range.

I had a macro (IIRC, also developed in xl97) where I couldn't get the last row
in that filter.

So I cheated...

I put some dummy text in the row after the real last row. Applied the filter to
that range, and then cleared the contents of that dummy cell.

I would always have (Blanks) as one of the options, but other than that, it
worked ok for me.

Dim LastRow as long
with worksheets("sheet1")
lastrow = .cells(.rows.count,"A").end(xlup).row
.cells(lastrow+1,"A").value = "dummy"
.range("A1:E" & lastrow + 1).autofilter
.cells(lastrow+1,"A").clearcontents
end with