vba 如何使用vba在Excel工作表中复制动态范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44242393/
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 dynamic range in Excel Sheet using vba
提问by Abdullah
I am trying to make the rang is dynamic in the macro without specifying the last line x.Sheets("SheetName").Range("A2:K1000").Copyin 1000 line I want to change it to dynamic because sometimes I have less or more than that.
我试图在宏中使 rang 是动态的，而不指定x.Sheets("SheetName").Range("A2:K1000").Copy1000 行中的最后一行我想将其更改为动态，因为有时我有更少或更多。
回答by UGP
Try this:
尝试这个：
Sub Test()
Dim lRow as Long
Dim sht as Worksheet
Set sht = x.Sheets("SheetName")
lRow = sht.Cells(sht.Rows.Count, 2).End(xlUp).Row
sht.Range("A2:K" & lRow).Copy
End Sub
回答by Vityata
Something like this will do the job:
像这样的事情将完成这项工作：
Option Explicit
Public Sub TestMe()
Dim lngLastRow As Long
lngLastRow = 150
'or come up with a function from here
'https://www.rondebruin.nl/win/s9/win005.htm
With x.Worksheets("SheetName")
.Range(.Cells(2, 1), .Cells(lngLastRow, 1)).Copy
End With
End Sub
In general, last row in a given column or last column in a given row is something, that you will do quite a lot of time in VBA. Thus, it is a good idea to read this: https://www.rondebruin.nl/win/s9/win005.htm
通常，给定列中的最后一行或给定行中的最后一列是您将在 VBA 中花费大量时间的事情。因此，阅读以下内容是个好主意：https: //www.rondebruin.nl/win/s9/win005.htm
回答by Vityata
Typically, look for the last row containing a value from the bottom up.
通常，自下而上查找包含值的最后一行。
with x.Sheets("SheetName")
.Range(.cells(2, "A"), .cells(.rows.count, "K").end(xlup)).Copy
'paste it somewhere
end with
