array-to-row autofilter issue

[Tekito]

Autofilter can give problems if a range containing both filtered and
unfiltered rows is being manipulated in a macro. So, in order to solve that,
I went back and added code to check which rows were hidden before running the
macros. It was tedious.

However, even changing cells one row at a time causes problems. I am
copying an array to a (single) row of cells, in order to reduce macro run
time. However, if some of the columns being edited are hidden (ordinary
hidden, not in relation to the autofilter), the copied values turn to mush.
This only happens if autofilter is on and the row is not hidden. Is there
any explanation or workaround?

If there isn't, then the bottom line is that some macros just can't be run
when autofilter is on, which imo is ridiculous. I seriously hope the way
autofilter affects the ranges in macros (by filtering out the hidden cells)
was not an intentional decision by Microsoft. That is *assuming* that the
only relevant cells are the ones currently visible- an assumption which, if
true, mildly simplifies the code, and, if false, completely breaks it. The
autofilter is a *UI* toggle feature. Since when is it okay to let UI
features deeply affect all other business code? And then why don't
'ordinary' hidden cells also create these problems? My background macros
have nothing to do with whatever the user has decided to filter for viewing.
Why did Microsoft decide to violate the tier separations? There should be
some option for being able to have your macros run as normal.

The standard answer seems to be just turn to autofilter off before a macro
run, which is fine, if the users don't mind their autofilters randomly being
erased everytime a macro runs in the background.

 

[Tekito]

I forgot to mention, the above was with Excel 2003.

 

[Tekito]

For further clarification, this is an example of an array to row macro, which
pastes the contents of an array all at once to an entire row. For a
worksheet with hidden columns and a row that is part of an autofilter (but is
still visible according to filter criteria) this produces bad data.


Sub ArraytoRow()
Dim dataarr() As Variant

ReDim dataarr(1 To 1, 1 To 256)
Dim i As Integer, j As Integer
For i = 1 To 1
For j = 1 To 256
dataarr(i, j) = j
Next
Next
Range("A12:IV12").Value = dataarr
End Sub

 

[Tekito]

...... or is there a more appropriate forum on microsoft's website that I can
direct this issue to?