vba 如何在vba中复制范围？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/461983/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I copy a range in vba?
提问by Dane O'Connor
I'm trying this but its giving me a type-mismatch. Is there another way to do this? I'm assuming rSource is not coming from the active sheet.
我正在尝试这个，但它给了我一个类型不匹配。有没有另一种方法可以做到这一点？我假设 rSource 不是来自活动工作表。
Also, I only need values.
另外，我只需要值。
Edit: The rSource is expected to have rows and columns. It will also be a single continuous area.
编辑： rSource 应该有行和列。它也将是一个单一的连续区域。
Edit: Once this operation is complete I should be able to filter each range independently.
编辑：此操作完成后，我应该能够独立过滤每个范围。
Function CopyRange(rSource As range) As range
' Declarations
Dim rTemp As range
' Create new range starting at cell A1
Set rTemp = Range(Cells(1, 1), Cells(rSource.Rows.Count, rSource.Columns.Count))
rTemp.Value = rSource.Value
Set CopyRange = rTemp
End Function
回答by Bravax
I believe your function is fine.
我相信你的功能很好。
I created a teststub:
我创建了一个测试存根：
Function Check()
Dim x As Range
Dim c As Range
Set c = Range("A1", "A2")
Set x = CopyRange(c)
End Function
With the cells A1 set to 2 and A2 set to 3.
单元格 A1 设置为 2，A2 设置为 3。
On stepping into this, and evaluating the x variable using:
?x.Range("A1")
(I got Type mismatch with ?x as you did.)
进入这个阶段，并使用以下方法评估 x 变量：
?x.Range("A1")
（我和 ?x 一样得到了类型不匹配。）
I get 2.
我得到 2。
Hope that helps.
希望有帮助。
回答by Adam Ralph
Assuming this code is in the code module for a Worksheet then it should work just fine. I just tried it and it runs fine for me (assuming the range passed to the function is contiguous).
假设此代码位于工作表的代码模块中，那么它应该可以正常工作。我刚刚尝试过，它对我来说运行良好（假设传递给函数的范围是连续的）。
Alternatively, try this:-
或者，试试这个：-
Function CopyRange(rSource As Range) As Range
Call rSource.Copy(Cells(1, 1))
Set CopyRange = Range(rSource.Address).Offset(1 - rSource.Row, 1 - rSource.Column)
End Function
回答by CABecker
Is this a continuous area? Is it bounded by empty cells? If so you should be able to use the currentregion property to set the range.
这是一个连续的区域吗？它是否以空单元格为界？如果是这样，您应该能够使用 currentregion 属性来设置范围。
I am thinking the reason you are getting an error is because you are not setting rSource in your first cells declaration.
我认为您收到错误的原因是您没有在第一个单元格声明中设置 rSource。
