Can you set .splitrow (correctly) on an autofiltered sheet?

[Oppressed1]

Run this code and watch the debug window! Why can't I set .splitrow to 3? It insists on setting it to 8. (Note, the code shows that that value is already 3. Then I set it to 3 (set it to the value that it already is), yet it insists on setting it to 8.)

I tested XL03; if your results vary with another version, please let me know that instead.

Please Login or Register  to view this content.

If you set the .SplitRow to less than 4, it's fine. If you set it to anything in (4 through 9) it simply sets the value to 8. If you set it to anything in (10 through highest visible row) it sets it to what you tell it. If you set it higher than the highest visible row number it sets it to the highest visible row number. All of these makes sense except when you try to set it to something in (4 through 9).

Here is my sad, demented theory: for the incorrect calculation Excel calculates .SplitRow by taking the total number of rows, minus the frozen number of rows, plus 1. Unfortunately, when a filter is on (or perhaps just when rows are hidden?), that causes a distorted calculation.

If indeed that or a different bug is in play, then I suppose that my workaround is to
save off .scrollrow and each .pane's .scrollrow;
walk through the .count of activesheet.autofilter.filters() and save a boolean of which ones are on;
turn them off;
do my thing with .splitRow's and .split and .freezepanes;
turn the filters back on;
restore .scrollrow and each .pane's .scrollrow.

Is that what I need to do?

By the way, I also tested setting .SplitRow with .FreezePanes and .Split both set to false, and it still sets it to 8, just as it does when .Split is True (and by the way, .Split changes to True when you set .SplitRow nonzero - which I hope helps at least one of us avoid some confused time wasting from not realizing that ).

[Oppressed1]

Ha. Instead of
ActiveWindow.SplitRow = 3
make that
ActiveWindow.SplitRow = ActiveWindow.SplitRow
to be truly amused or upset. That command also barbeques activewindow.visibleRange.row and activewindow.scrollRow. Try changing
Debug.Print "before: "; ActiveWindow.SplitRow
to
Debug.Print "before: "; ActiveWindow.SplitRow,activewindow.visibleRange.row,activewindow.scrollRow
and change the "after" also. Brace for weirdness.

Isn't that more than odd?

[Oppressed1]

Additional question in case I have .scroll and .visibleRange gurus reading this: Are activewindow.visibleRange.row and activewindow.scrollRow strictly identical? ( and activewindow.visibleRange.column and activewindow.scrollcolumn ) Would it be redundant to manage one if you're already managing the other?