vba 如何复制可变范围vba
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23305818/
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 to copy variable range vba
提问by Buras
My table contains variable number of rows and three columns (A:C) that I am interested in
我的表包含我感兴趣的可变行数和三列 (A:C)
A B C D
1 xx xx xx xxx
2 ....
3 ....
4 ...
I need to copy from WorkSheet1to WorkSheet2
我需要从复制WorkSheet1到WorkSheet2
ws2.Range("A1").Value = ws1.Range("A1:C4").Value
The problem is that I do not want to hardcode C4, because it can be C5or C20. How can I account for possible variable number of rows.
问题是我不想硬编码C4，因为它可以是C5或 C20。我如何考虑可能的可变行数。
PS : I cannot use Range("A1").CurrentRegionbecause this will select more columns than needed, i.e. column Dvalues will also get selected. Although it will select the correct number of rows
PS：我不能使用，Range("A1").CurrentRegion因为这将选择比需要更多的列，即列D值也将被选中。虽然它会选择正确的行数
回答by David Zemens
There is more than one solution to most problems, but since CurrentRegionmethod returns the right number of rows (but the wrong number of columns), you could do something like this assuming you always need THREE columns.
大多数问题有不止一种解决方案，但由于CurrentRegion方法返回正确的行数（但错误的列数），假设您总是需要三列，您可以执行类似的操作。
Dim rng as Range
Set rng = ws1.Range("A1").CurrentRegion.Resize(,3)
ws2.Range(rng.Address).Value = rng.Value
回答by jmstoker
I'm making an assumption that the last row in D will be equal to or less than the last row of A:C. I'm also assuming that the last row is determined by the last row that contains data.
我假设 D 中的最后一行将等于或小于 A:C 的最后一行。我还假设最后一行由包含数据的最后一行确定。
Sub CopyColumnAtoC()
Dim lastRow As Long
lastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ThisWorkbook.Worksheets(1).Range("A1:C" & lastRow).Copy
ThisWorkbook.Worksheets(2).Range("A1:C" & lastRow).PasteSpecial xlPasteValues
End Sub
回答by ob1quixote
You can specify just columns in a range. So
您可以仅指定范围内的列。所以
Sheet2.Range(Sheet1.Range("A:C").Address).Value = Sheet1.Range("A:C").Value
will copy columns A through C regardless of the rows in each column, i.e.if Column A has 8 rows, B has 6, and C has 11 it will still work.
无论每列中的行如何，都将复制 A 列到 C 列，即如果 A 列有 8 行，B 有 6 行，C 有 11 行，它仍然可以工作。
