vba lastrow 和 excel 表。
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43631926/
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
lastrow and excel table.
提问by DL1
I have tried to define a lastrow code that would return the last row value of the last non empty cell in a excel table. (ITS FORMATTED AS A TABLE)
我试图定义一个 lastrow 代码，该代码将返回 Excel 表格中最后一个非空单元格的最后一行值。（它被格式化为表格）
My exceltable have values in COL A from 1 to 1005, COL B from 1 to 414 and in COL C from 414 to 1005.
我的 exceltable 在 COL A 中的值从 1 到 1005，COL B 从 1 到 414，在 COL C 中的值从 414 到 1005。
What i want is to have one lastrow code that returns 414 and one that returns 1005. The problem i get is that because it is in a table. my codes
我想要的是有一个返回 414 的 lastrow 代码和一个返回 1005 的代码。我得到的问题是因为它在一张表中。我的代码
lastrow3 = ThisWorkbook.Worksheets("Data?nskem?l").Range("A" & Rows.Count).End(xlUp).Row
lastrow2 = ThisWorkbook.Worksheets("Data?nskem?l").Range("B" & Rows.Count).End(xlUp).Row
Both return 1005. Can i get around this with my table or is it a formating issue of some sort?
两者都返回 1005。我可以用我的表解决这个问题还是某种格式问题？
Best regards and thanks in advance /D
最好的问候和提前致谢 /D
回答by Subodh Tiwari sktneer
You will have issue if there is data below the excel table on the sheet. It's always better to refer the table column while finding the last row in an excel table.
如果工作表上的 excel 表格下方有数据，您将遇到问题。在 Excel 表格中查找最后一行时，最好参考表格列。
Sub FindLastRowInExcelTableColAandB()
Dim lastRow1 As Long, lastRow2 As Long
Dim ws As Worksheet
Set ws = Sheets("Data?nskem?l")
'Assuming the name of the table is "Table1"
lastRow1 = ws.ListObjects("Table1").Range.Columns(1).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lastRow2 = ws.ListObjects("Table1").Range.Columns(2).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End Sub
回答by Ambie
Finding last rows of tables is a bit fiddly, especially as you often need to cater for the case that a user has filtered the data. A loop with multiple checks might suit you better as you can adjust it to your own needs for the data within the table.
查找表的最后一行有点繁琐，尤其是当您经常需要满足用户过滤数据的情况时。具有多项检查的循环可能更适合您，因为您可以根据自己对表中数据的需求进行调整。
You also don't mention whether you can be certain that the last row is indeed a table.
您也没有提到您是否可以确定最后一行确实是一个表格。
In view of these points, perhaps the .Findfunction will suit you as it will find any non-empty cell whether in a table or not and whether hidden or not (though it doesn't cope with a filtered table). (It's not quite true to say "any non-empty cell", as a null string, for example, wouldn't be picked up, but maybe these exceptions won't trouble you). Anyhow your code could be:
鉴于这些点，也许该.Find函数会适合您，因为它会找到任何非空单元格，无论是否在表格中，是否隐藏（尽管它不处理过滤表格）。（说“任何非空单元格”并不完全正确，例如，空字符串不会被拾取，但也许这些异常不会给您带来麻烦）。无论如何，您的代码可能是：
With Sheet1
lastRow1 = .Columns(1).Find(What:="*", _
After:=.Columns(1).Cells(1), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
lastRow2 = .Columns(2).Find(What:="*", _
After:=.Columns(2).Cells(1), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End With
回答by Shai Rado
Try the code below to get the last row in Column A and Column B from a Table (ListObject):
尝试使用下面的代码从表 ( ListObject) 中获取 A 列和 B 列中的最后一行：
Option Explicit
Sub LastRowTable()
Dim Tbl As ListObject
Dim LastRow2 As Long, LastRow3 As Long
' modify "Table1" to your table's Name
Set Tbl = ThisWorkbook.Worksheets("Data?nskem?l").ListObjects("Table1")
LastRow3 = Tbl.ListColumns(1).Range.Rows.Count '<-- last row in Column A in your Table
LastRow2 = Tbl.ListColumns(2).Range(LastRow3, 1).End(xlUp).Row '<-- last row with data in Column B in your Table
End Sub
回答by shakespeare
try this
尝试这个
Dim DataRange As Range
Set DataRange = Range("A1:M" & LastRow)
