VBA 列中的最后一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39470412/
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
Last row in column VBA
提问by Probs
I wrote a short VBA code to automate stuff.
A short snippet is as follows:
我写了一个简短的 VBA 代码来自动化操作。
一个简短的片段如下：
Sub TEST()
Rows("1:2").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A:$O8"), , xlYes).Name = _
"Table2"
End Sub
However, every Excel file differs with regards to the number of rows. Now when I recorded this macro it just takes the range of $A$1:$O$148. How can I adjust this part so that it automatically recognizes the last row and/or range?
但是，每个 Excel 文件的行数都不同。现在，当我录制这个宏时，它只需要 $A$1:$O$148 的范围。如何调整此部分以使其自动识别最后一行和/或范围？
I already tried:
我已经尝试过：
.Range("A1").SpecialCells(xlCellTypeLastCell).Row
Instead of:
代替：
Range("$A:$O8")
Thanks in advance!
提前致谢！
回答by InternInNeed
Generally, you can find the last row / column and therefore the complete used range by using:
通常，您可以使用以下方法找到最后一行/列，从而找到完整的使用范围：
ActiveWorkbook.Worksheets("NAME").Range("A" & Rows.Count).End(xlUp).row
ActiveWorkbook.Worksheets("NAME").Range("A" & Rows.Count).End(xlUp).row
for the last row and
对于最后一行和
ActiveWorkbook.Worksheets("NAME").Cells(1, Columns.Count).End(xlToLeft).Column
ActiveWorkbook.Worksheets("NAME").Cells(1, Columns.Count).End(xlToLeft).Column
for the last column.
I would advice against using UsedRangebecause if you have blanks in between, it will lead to mistakes.
对于最后一列。我建议不要使用，UsedRange因为如果中间有空格，则会导致错误。
回答by Matt Cremeens
This is the way I do it and I'm guessing this is a duplicate, but you can mimic hitting End-Upfrom a row well below your used range with
这是我这样做的方式，我猜这是重复的，但是您可以模拟End-Up从远低于使用范围的行中击球
finalRow = Range("A65000").End(xlup).Row
then you can do
那么你可以做
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A:$O$" & finalRow & ""), , xlYes).Name = _
"Table2"
回答by codeguy
You can use the UsedRangeproperty of your worksheet-object.
You can get the indexes of your last used row and column using
您可以使用UsedRange工作表对象的属性。您可以使用获取上次使用的行和列的索引
ActiveSheet.UsedRange.Columns.Count
ActiveSheet.UsedRange.Rows.Count
So you would basically use this like
所以你基本上会像这样使用它
With m_Sheet
' Use this range
.Range(.UsedRange.Rows.Count, .UsedRange.Columns.Count)
End With
回答by Dominique
I used the shortcut Ctrl+Arrow Down, which resulted in following VBA (after recording the macro):
我使用了快捷方式Ctrl+Arrow Down，这导致了以下 VBA（在录制宏之后）：
Selection.End(xlDown).Select
回答by Dominique
This will do the trick:
这将解决问题：
Dim rSource As Range
With Worksheets("Sheet1")
Set rSource = .Range("A1", .Columns("A:O").Find(What:="*", After:=.Range("A1"), SearchDirection:=xlPrevious))
End With
回答by William Tong
This would help if you don't know much of the vba library or syntax like me.
如果您像我一样不太了解 vba 库或语法，这会有所帮助。
Dim lastline as long
Dim lastColumnline as long
dim usedcells as long
dim i as long
dim YOURCOLUMN as long
dim count as long
Set ws = Worksheet("blablabla")
lastline = ws.UsedRange.SpecialCells(xlCellTypeLastCell).Row
usedcells = Application.WorksheetFunction.CountA(ws.Columns(YOURCOLUMN))
for i = 1 to lastline
if ws.cells(i,YOURCOLUMN) <> vbnullstring then
count = count + 1
if count = usedcells then
lastColumnline = i
'i is the lastColumnline
end if
end if
next i
回答by Vityata
This is how I locate last row:
这是我如何找到最后一行：
Function lastRow(Optional strSheet As String, Optional column_to_check As Long = 1) As Long
Dim shSheet As Worksheet
If strSheet = vbNullString Then
Set shSheet = ActiveSheet
Else
Set shSheet = Worksheets(strSheet)
End If
lastRow = shSheet.Cells(shSheet.Rows.Count, column_to_check).End(xlUp).Row
End Function
The column is optional, if no value is given, it is column A. My GitHub repository with LastThings is here.
该列是可选的，如果没有给出值，则它是A列。我的带有 LastThings 的 GitHub 存储库在这里。
