存在过滤器时使用数组设置范围的值 [英] Set value of a range using array when filter present

问题描述

我想将范围的值放入数组中，处理数组数据，然后考虑到范围中可能包含一些隐藏的行，然后将该数组中的值重新设置为它来自的范围.

I want to get the values of a range into an array, work on the array data, and then set the values in that array back into the range it came from, considering that the range may contain some hidden rows.

让我们以两列的工作表为例:

Let's take an example of a sheet of two columns:

----A-------B-----
    VALUE | FILTER
    1     | P
    2     | N
    3     | N
    4     | P
    5     | P

然后，您应用自动过滤器并将B列设置为仅显示带有"P"的单元格，这将隐藏第3行和第4行.

Then you apply an auto filter and set the B column to show only cells with "P", that will hide rows 3 and 4.

我得到的范围是这样的:

I get that range like this:

Sub WorkRange()
    Dim R As Range

    Set R = ActiveSheet.Range("A2:A6")
    Values = R.Value

    Values(1, 1) = "New 1"
    Values(2, 1) = "New 2"
    Values(3, 1) = "New 3"
    Values(4, 1) = "New 4"
    Values(5, 1) = "New 5"

    R.Value = Values
End Sub

该代码将获取从A2到A6的值，包括由AutoFilter定义过滤掉的隐藏行.当我尝试使用更改后的数据设置该范围的值时，它将使隐藏的行保持不变，并将可见的单元格(单元格A2，A5和A6)设置为新建1".

That code will get the values from A2 to A6, including hidden rows filtered out by the AutoFilter definition. When I try to set the value of that range with the changed data, it leaves hidden rows unchanged and sets visible cells (cells A2, A5 and A6) to "New 1".

无论行状态如何，我都希望在该范围内设置值.

I want to set the values in that range regardless of the row state.

我有一些带有数千行的工作表.如果我使用.Cells逐行更改，则将花费太长的时间.使用数组设置范围值的速度要快得多，并且在不存在过滤器的情况下也可以使用.

I have some sheets with thousands of rows. If I change using .Cells, row by row, it takes too long. Using Arrays to set the Value of a Range is much faster, and it works in cases where a filter is not present.

推荐答案

将数组结果应用到另一张表中，并使用 vlookup 或 = refercells 在公式中引用这些范围

Apply the array result into a different sheet and refer those range in fomula with vlookup or =refercells

range("A2:A"& lrow).formula ="= sheetnamecell"

这是将数组结果应用回过滤范围的唯一方法.如果未过滤数组范围，则可以将结果直接应用于该范围.

This is the only way to apply the array result back to the filtered range. If the array range is not filtered you can just apply the result directly to the range.

这篇关于存在过滤器时使用数组设置范围的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！