Securing that autofilter does not show the last row, which is hidden, when show all is selected
Posted on 2013-10-30
I have a sheet, that is send to coleagues, which in the input sheet has the autofilter applied.
The last line in the input sheet is hidden, and they will need to hit an insert line button to get new lines in the sheet.
Insert function is deactivated, and the last line is hidden, to secure that all calculation rules will be followed in new input lines.
But when I first activate the filter with some criteria, and then sets the autofilter to show all, my hidden line is shown.
for instance if my input data is in line a1to P10, I want the autofilter range to select this area. And even if line 11 (which is hidden) is using the same datavalidation rules as the 10 above, I do not wish to include that in my autofilter.
And if I insert an extra line the autofilter should now be applied to a1 to P11, and line 12, which is now hidden should still not be included in my autofilter.
Can that be done in Excel without VBA? Can I do it with VBA, if that is not a possibility?