vba 复制单元格范围并粘贴到第一个空行更有效的代码？电子表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17972106/
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
copying range of cells and pasting to the first empty row more effective code? Excel
提问by Taylor Zwick
I have a Macro that takes a selection of cells from "New Search" and paste the range to the first open cell in "Past Searches" I'm thinking that my code is very ineffective. Does anyone have any ideas of how I can improve this?
我有一个宏，它从“新搜索”中选择单元格并将范围粘贴到“过去搜索”中的第一个打开的单元格中，我认为我的代码非常无效。有没有人对我如何改进这一点有任何想法？
Sub Macro5()
Range("A3:J3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Past Searches").Select
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Worksheets("New Searches").Activate
Application.CutCopyMode = False
End Sub
回答by Fabio Pereira
First, you don't have to use .Select or .Activate on cells/worksheets that you'll manipulate, just reference them directly. That can really slow down your code.
首先，您不必在要操作的单元格/工作表上使用 .Select 或 .Activate ，只需直接引用它们即可。这确实会减慢您的代码速度。
The .Copy method can take a Destination parameter, so you don't have to use .Paste somewhere else.
.Copy 方法可以采用 Destination 参数，因此您不必在其他地方使用 .Paste。
Also, the .End property isn't very reliable. It's better to use UsedRange instead.
此外， .End 属性不是很可靠。最好改用 UsedRange。
You can do that pretty much in only 1 line.
你几乎可以在 1 行中做到这一点。
Worksheets("New Search").Range("A3:J3").Copy Destination:=Worksheets("Past Searches").UsedRange.Columns(1).Offset(1, 0)
回答by Taylor Zwick
Fabio thanks for pointing me in the right direction, I wouldn't have been able to turn that mess in to concise code.
Fabio 感谢您为我指明了正确的方向，我无法将这种混乱变成简洁的代码。
numofrows = ActiveSheet.UsedRange.Rows.Count`
Worksheets("New Searches").Range("A3", "J" + CStr(numofrows)).Copy Destination:=Worksheets("Past Searches").Range("A1").End(xlDown).Offset(1, 0)
This ended up doing what I need to do.
这最终做了我需要做的事情。
回答by MakeCents
You could also do this so you don't need numofrows?
你也可以这样做，所以你不需要 numofrows？
Worksheets("New Searches").Range(Range("A3:J3"), Range("A3:J3").End(xlDown)).Copy Destination:= _
Worksheets("Past Searches").Range("A1").End(xlDown)
