是否可以将一定范围的变量转储到工作表中？

I have a variant like these

我有这样的变种

var = sheet1.Range("A1:P3600").Value

I have done some operarions and pushed the unwanted rows to the top in the variant. Now I have to copy the var variant to the other sheet from a certain range.

我做了一些操作,并将不需要的行推到了变体的顶部。现在我必须将var变体复制到特定范围内的另一个工作表。

sheet1.range("A3444:P" & i).value = var(range(cells(r,"A").cells(l,"P"))

that is say var(350 to end of var) should be copied to the other sheet. Is it possible ? can we do like that ?

也就是说应该将var(350到var的结尾)复制到另一个工作表。可能吗 ?我们能这样做吗?

2 个解决方案

#1

One way is to dump the reduced array to a second array, then the second array to your range

一种方法是将缩小的数组转储到第二个数组,然后将第二个数组转储到您的范围

The code below makes a variant array with 3600 rows by 16 columns (ie A:P), data is dumped into the array for sample data (note you already have this array as Var), then a variable is used as a marker to reduce the array to a second array, the second array is then written to the range.

下面的代码生成一个3600行乘16列(即A:P)的变量数组,数据被转储到数组中以获取样本数据(注意您已将此数组作为Var),然后使用变量作为标记来减少将数组转换为第二个数组,然后将第二个数组写入该范围。

Updated to match your exact data locations. In your case you have Var1 already (your Var), so you just need the second portion of the code that starts at lngStop = 350 and make my code Var1 references Var

已更新,以匹配您的确切数据位置。在你的情况下你已经有Var1(你的Var),所以你只需要从lngStop = 350开始的代码的第二部分并使我的代码Var1引用Var

Sub TestME()
Dim Var1
Dim Var2
Dim lngCnt As Long
Dim lngCnt2 As Long
Dim lngCnt3 As Long
Dim lngCnt4 As Long
Dim lngStop As Long
Var1 = Sheet1.Range([a1], [p3600]).Value2
For lngCnt = 1 To UBound(Var1, 1)
    For lngCnt2 = 1 To 16
        Var1(lngCnt, lngCnt2) = "I am row " & lngCnt & " column " & lngCnt2
    Next lngCnt2
Next lngCnt
lngStop = 350
ReDim Var2(1 To UBound(Var1, 1) - lngStop + 1, 1 To UBound(Var1, 2))
For lngCnt3 = lngStop To UBound(Var1, 1)
    For lngCnt4 = 1 To UBound(Var1, 2)
        Var2(lngCnt3 - lngStop + 1, lngCnt4) = Var1(lngCnt3, lngCnt4)
    Next lngCnt4
Next lngCnt3
Sheet1.[a3444].Resize(UBound(Var2, 1), UBound(Var2, 2)).Value2 = Var2
End Sub

#2

You can slap only a portion of your array onto the sheet, but only if that portion is at the top left of your array, i.e. only the first n columns and the first m rows. There is no straightforward way of slapping the the last n columns and the last m rows. In this case, you have to resort to transferring stuff to a second, smaller array, and then dump that onto the sheet, as in @brettdj's answer -- this works fine, but it's a bit roundabout and too much coding for my taste.

您只能将数组的一部分打到工作表上,但前提是该部分位于数组的左上角,即只有前n列和前m行。没有简单的方法来拍打最后n列和最后m行。在这种情况下,你不得不求助于将东西转移到第二个较小的数组,然后将其转储到工作表上,就像@ brettdj的答案一样 - 这很好用,但它有点迂回,而且编码太多我的口味。

Instead, if you could push your "unwanted rows" down to the bottom of your array, then it would be a one-liner to slap the top rows of that array onto a sheet (omitting the last 350).

相反,如果你可以将你的“不需要的行”向下推到数组的底部,那么将该数组的顶行打到一张纸上将是一个单行(省略最后的350行)。

Here's an example where a 4 x 3 array is read in, and only the top-left 3 x 2 is slapped back onto the sheet. The secret is to make the target range smaller than the entire array.

这是一个读入4 x 3阵列的示例,只有左上角的3 x 2被打回到纸张上。秘诀是使目标范围小于整个阵列。

Dim v
v = Range("A2:C5")
Range("E2:F4") = v

#1