vba 复制到最后一条数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5327265/
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
Copy until the last piece of data
提问by CustomX
Is it possible to select a range to copy to the last item?
是否可以选择一个范围来复制到最后一个项目？
Sheets("Design").Select
Range("A1:C200").Copy
'My data only runs until E48 and Im left with 152 blancs.
So now it copies from A1 to E200, but how can I edit the above code so it only selects until the last piece of data, which in my case is E48? (this is variable)
所以现在它从 A1 复制到 E200，但是我怎样才能编辑上面的代码，使它只选择到最后一段数据，在我的例子中是 E48？（这是可变的）
Thanks!
谢谢！
@Jean
@让
In my excel sheet I have data running from A1-A18, B is empty and C1-C2. Now I would like to copy all the cells that contain a value.
在我的 excel 表中，我有从 A1-A18 运行的数据，B 为空，C1-C2。现在我想复制所有包含值的单元格。
With Range("A1")
Range(.Cells(1, 1), .End(xlDown).Cells(20, 3)).Copy
End With
This will copy everything from A1-C20, but I only want A1-A18 and C1-C2 seen as though these contain data. But it needs to be formed in a way that once I have data in B or my range extends, that these get copied too.
这将复制 A1-C20 中的所有内容，但我只希望 A1-A18 和 C1-C2 被视为包含数据。但是它需要以一种方式形成，一旦我在 B 中有数据或我的范围扩展，这些数据也会被复制。
Is this perhaps a bit clearer?
这也许更清楚一点？
About the Copy/Paste situation;
关于复制/粘贴情况；
I currently paste using this
我目前使用这个粘贴
appWD.Selection.PasteSpecial ' So it copies the formats too?
'In your case, do you mean I should do it like this?
Range(.Cells(1, 1), .End(xlDown).Cells(20, 3)).Copy Destination:=appWD.Selection.PasteSpecial
回答by paulmorriss
This should work:
这应该有效：
a1 = Range("a1").Address
lastcell = Range("E1").End(xlDown).Address
Range(a1, lastcell).Copy
回答by Jean-Fran?ois Corbett
This works:
这有效：
With Range("A1")
Range(.Cells(1, 1), .End(xlDown).Cells(1, 5)).Copy
End With
Change the "5" if you need a different number of columns.
如果您需要不同的列数，请更改“5”。
Also, this I learned the hard way: Avoid Copy/Pasteif at all possible! Copy and Paste use the clipboard. Other programs may read from / write to the clipboard while your code is running, which will cause wild, unpredictable results. However, it's safe to copy to your target range on the same line, i.e. do this
此外，这是我通过艰难的方式学到的：尽可能避免复制/粘贴！复制和粘贴使用剪贴板。其他程序可能会在您的代码运行时从剪贴板读取/写入剪贴板，这将导致疯狂的、不可预测的结果。但是，在同一行上复制到您的目标范围是安全的，即执行此操作
Range(.Cells(1, 1), .End(xlDown).Cells(1, 5)).Copy myDestinationRange
and not this
而不是这个
Range(.Cells(1, 1), .End(xlDown).Cells(1, 5)).Copy
myDestinationRange.Paste
The latter uses the clipboard while the former does not.
后者使用剪贴板，而前者不使用。
回答by Patrick Honorez
If that suits you, my favorite is CurrentRegion:
如果这适合你，我最喜欢的是 CurrentRegion：
Range("A1").CurrentRegion.Copy Destination:=Sheet2.Range("a1")
