vba 需要一个“简单”的 excel 宏来查找列中的底部单元格，创建一个范围并复制它
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9899519/
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
Need a "Simple" excel macro to find bottom cell in a column, create a range and copy it
提问by CJG
I have a spreadsheet I'm using to compile text that changes all the time.
我有一个电子表格，用于编译一直在变化的文本。
In column AD, Row 4(AD4) I put the contents of text, and it can have data going 1000 to 4000 rows down. It changes every time, so there is no static range name. I need a macro that
在 AD 列的第 4(AD4) 行中，我放置了文本内容，它可以将数据向下移动 1000 到 4000 行。它每次都会改变，因此没有静态范围名称。我需要一个宏
- finds the final piece of data in that column,
- then automatically "drags a box" from that spot two columns to the left (AB4)
- and copies it... (A 3000 row piece of text would be AB4:AD3004) (Macro stops there, with text to be copied highlighted)
- 找到该列中的最后一段数据，
- 然后自动从那个位置向左“拖动一个框”两列（AB4）
- 并复制它...（一段 3000 行的文本将是 AB4:AD3004）（宏停在那里，要复制的文本突出显示）
The current version finds the bottom cell correctly, but if I run the macro a 2nd time, with new data, it keeps trying to copy the same range. (I used the Formula Define.Name method, to name the cell, and then selected AB4:LastRow) but it is ALWAYS 3160 whether data goes to row 4000 or not.....
当前版本正确找到底部单元格，但如果我第二次运行宏，使用新数据，它会不断尝试复制相同的范围。（我使用了 Formula Define.Name 方法来命名单元格，然后选择 AB4:LastRow）但无论数据是否进入第 4000 行，它始终是 3160.....
Sub Last_row()
Cells(Application.Rows.Count, 30).End(xlUp).Select
' following lines of code are useless
Range("AB4:AD3160").Select
Range("AD3160").Activate
Selection.Copy
End Sub
回答by Reafidy
To answer your question directly:
要直接回答您的问题：
With Sheet1
.Range("AB4", .Cells(Rows.Count, "AD").End(xlUp)).Copy
End With
Copy to specific location WITHOUTusing clipboard:
不使用剪贴板复制到特定位置：
With Sheet1
.Range("AB4", .Cells(Rows.Count, "AD").End(xlUp)).Copy Sheet2.[A1]
End With
Copy and exclude formatting:
复制和排除格式：
With Sheet1
With .Range("AB4", .Cells(Rows.Count, "AD").End(xlUp))
Sheet2.Cells(1, "A").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
End With
Note:Replace all sheet codenames (sheet1, Sheet2) above with your actual sheet codenames.
注意：将上面的所有工作表代号（工作表 1、工作表 2）替换为您的实际工作表代号。
回答by brettdj
Your current code hard-codes the range of interest with
Range("AB4:AD3160").Select
您当前的代码对感兴趣的范围进行硬编码
Range("AB4:AD3160").Select
This code will define a dynamic range starting from AB4 to the last non-empty cell in column AD
此代码将定义从 AB4 开始到 AD 列中最后一个非空单元格的动态范围
You can then use this range (without selecting) for changing values elsewhere (note that you may not need to actually copyrng1, it is possible to dump these values to a separate range directly without a copy and paste.
然后，您可以使用此范围（无需选择）更改其他地方的值（请注意，您可能不需要实际复制rng1，可以将这些值直接转储到单独的范围，而无需复制和粘贴。
Sub Last_row()
Dim rng1 As Range
Set rng1 = Range([ab4], Cells(Rows.Count, 30).End(xlUp))
rng1.Copy
End Sub
Update:Example of how to copy a dynamic sized range from one sheet to another without a copy and paste:
更新：如何在不复制和粘贴的情况下将动态大小范围从一张纸复制到另一张纸的示例：
Sub Last_row2()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range
Set ws1 = Sheets(1)
Set ws2 = Sheets(2)
Set rng1 = ws1.Range(ws1.[ab4], ws1.Cells(Rows.Count, 30).End(xlUp))
ws2.[a1].Resize(rng1.Rows.Count, rng1.Columns.Count).Value = rng1.Value
End Sub
