vba 使用vba excel查找最后一个单元格地址
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11453658/
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
Find the last cell address using vba excel
提问by Adam Falkowski
I have searched this site and it seems like all the answers just point to finding the row number of the cell.
我搜索了这个网站，似乎所有的答案都指向找到单元格的行号。
I am trying to set a range so that it will go from A1to the end of the data in the Acolumn. This spreadsheet will be updated weekly so there will be data added to it every week.
我正在尝试设置一个范围，以便它从列A1中的数据开始到末尾A。该电子表格将每周更新一次，因此每周都会向其中添加数据。
I was wondering what code would work so that I can either find the row number and somehow tie it in with my range code so that it will equal "A" + lastrownumber? OR if there is code that will provide the column and row number together? If I have missed the link to the correct answer a simple link will do as well and I apologize for the post and your time.
我想知道什么代码可以工作，以便我可以找到行号并以某种方式将它与我的范围代码联系起来，以便它等于"A" + lastrownumber？或者是否有代码可以一起提供列号和行号？如果我错过了正确答案的链接，一个简单的链接也可以，我为这篇文章和你的时间道歉。
Here is my code:
这是我的代码：
Sub NamedRange()
Dim Rng1 As Range
Dim newDate As Integer
Dim NumberOfRows As Range
Dim MyRange As Range
Dim lastRow2 As Range
lastRow2 = Range("A65536").End(xlUp).Row
'lastRow2 = LastRow
Set Rng1 = Sheets("Sheet1").Range(lastRow2)
ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:=Rng1
Dim date1 As String
Dim dat As Date
Dim newPrice As Double
Set RgSales = Range("MyRange")
回答by mkingston
This will return the range object corresponding to the last filled in cell in column A
这将返回与 A 列中最后填充的单元格相对应的范围对象
Range("A:A").Find("*",Range("A1"),SearchDirection:=xlprevious)
If you want the row number, use the following:
如果需要行号，请使用以下命令：
Range("A:A").Find("*",Range("A1"),SearchDirection:=xlprevious).row
回答by SeanC
This will give the last row in a given column
这将给出给定列中的最后一行
= Cells(Activesheet.Rows.Count, ColumnNumber).End(xlUp).Row(Fixed per @Gimp)
= Cells(Activesheet.Rows.Count, ColumnNumber).End(xlUp).Row（每个@Gimp 固定）
you then have a reference you can use to add to the data - e.g if you want to look in column "A", then that would be columnnumber 1. feed that into the function, then you can use Cells(NumberReturnedFromFunction,ColumnNumber)to address that cell, and add .Addressif you want the A1style of address
然后你有一个参考，你可以用来添加到数据中 - 例如，如果你想查看列“A”，那么这将是 columnnumber 1。将它输入到函数中，然后你可以Cells(NumberReturnedFromFunction,ColumnNumber)用来寻址该单元格，然后添加.Address如果你想要A1地址的风格
回答by danielpiestrak
Try using something like this:
尝试使用这样的东西：
Activesheet.Cells(Activesheet.Rows.Count, "A").End(xlUp).Row
You can replace Activesheet with references to a sheet's index # like Sheets(1)or the sheet name like Sheets("Sheet1")
您可以使用对工作表索引 #likeSheets(1)或工作表名称的引用替换 ActivesheetSheets("Sheet1")
By using the Rows.Countit will check to see what the max rows are and be compatible across all versions of Excel.
通过使用Rows.Count它，它将检查最大行数是多少，并在所有版本的 Excel 中兼容。
In the end you can use this within your range reference like this:
最后，您可以在您的范围参考中使用它，如下所示：
Msgbox Sheets(1).Range("A" & Sheets(1).Cells(Sheets(1).Rows.Count, "A").End(xlUp).row).value
But I'd probably rewrite that as
但我可能会把它改写为
With Sheets(1)
Msgbox .Range("A" & .Cells(.Rows.Count, "A").End(xlUp).row).value
End With
回答by whytheq
In case there are gaps in the data I'd avoid using xlDown so something like the following is fine. Try it in the immediate window:
如果数据中存在空白，我会避免使用 xlDown，因此类似以下内容就可以了。在即时窗口中尝试：
Activesheet.range("A1:A" & Activesheet.Cells(Excel.Rows.Count, 1).End(Excel.xlUp).Row).select
