Excel / VBA 最后一行/列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16359593/
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
Excel / VBA last row/column
提问by lcrmorin
I need to copy paste big sheets, this is taking a lots of time. I have been advised not to use .copy process on worksheets but rather proceeds cell by cell. Giving new cells each properties of the ancient cell. This is what I do here: Saving only some sheets in another Workbook.
我需要复制粘贴大张纸，这需要很多时间。我被建议不要在工作表上使用 .copy 过程，而是逐个单元地进行。赋予新细胞古代细胞的各种特性。这就是我在这里所做的：在另一个 Workbook 中只保存一些工作表。
To do it cells by cells I need to know the last cell containing information. (not only value, but colour, borders... etc). I've seen many simple solution on internet, but they all have a problem.
要逐个单元地执行此操作，我需要知道包含信息的最后一个单元格。（不仅是价值，还有颜色、边框......等）。我在互联网上看到了许多简单的解决方案，但它们都有问题。
ActiveSheet.UsedRange.Rows.Count often gives too many values... I got a 810 * 16000 answer for a 5 * 18 table
ActiveSheet.UsedRange.Rows.Count 经常给出太多的值......我得到了一个 810 * 16000 的 5 * 18 表的答案
range("A" & activesheet.rows.count).end(xlup).row works only for the first columns...
range("A" & activesheet.rows.count).end(xlup).row 仅适用于第一列...
What would be the best way to procedd to final the real last line with value ? containig information (value, colour, border...)
用 value 处理最后一行的最佳方法是什么？包含信息（值、颜色、边框...）
回答by Steven
This command in Excel 2010 ActiveCell.SpecialCells(xlLastCell).Selectwill move the cursor (active cell) to the last one that had a non-trivial value, even if the cell currently is blank
Excel 2010 中的此命令ActiveCell.SpecialCells(xlLastCell).Select会将光标（活动单元格）移动到具有重要值的最后一个，即使该单元格当前为空白
This command Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Selectwill select all cells from the current to the last one that had a non-trivial value.
此命令Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select将选择从当前到最后一个具有非平凡值的所有单元格。
回答by Tom2355524
Both these approaches work for me for getting the last cell 2007. I have used the "UsedRange" method in Excel 2003 as well.
这两种方法都适用于我获取最后一个单元格 2007。我也在 Excel 2003 中使用了“UsedRange”方法。
If they do not work for you then your spreadsheet may have something in it that Excel is not showing you. This has happened to me before. The fix would be to select every empty row below your real data and right-Click->Delete them (Same with columns to the right).
如果它们对您不起作用，那么您的电子表格中可能包含 Excel 未向您显示的内容。我以前也遇到过这种情况。解决方法是选择真实数据下方的每个空行，然后右键单击-> 删除它们（与右侧的列相同）。
Shortcut to delete rows: Shift+Space, Shift+Control+DownArrow, Rightclick->Delete
删除行的快捷键：Shift+Space、Shift+Control+DownArrow、Rightclick->Delete
Shortcut to delete columns: Control+Space, Shift+Control+RightArrow, Rightclick->Delete
删除列的快捷方式：Control+Space、Shift+Control+RightArrow、Rightclick->Delete
examples:
例子：
set lastCell = ActiveCell.SpecialCells(xlLastCell)
or
或者
Set lastCell = worksheetObj.UsedRange.Item(worksheetObj.UsedRange.Cells.Count)
回答by PSN
Save the Following code to a class file name FinalRowLocator
将以下代码保存到类文件名 FinalRowLocator
Public Property Get FinalRow(Optional ByVal Col As String, Optional ByVal Min As Boolean) As Long
FinalRow = pFinalRow(Col, Min)
End Property
Public Property Get Verify(Optional ByVal Col As String, Optional ByVal Min As Boolean) As Long
Verify = pVerify(Col, Min)
End Property
Private Function pVerify(Optional ByVal Col As String, Optional ByVal Min As Boolean) As Long
Dim i As Long
Dim j As Long
Dim rVerify As Long
Dim Votes(1 To 5) As Byte
Dim Congress(1 To 5) As Long
Dim FRL As New FinalRowLocator
Congress(1) = FRL.Columbus
Congress(2) = FRL.GosEgg
Congress(3) = FRL.OldTimer
Congress(4) = FRL.RainMan
Congress(5) = FRL.Slacker
For i = 1 To 5
For j = 1 To 5
If Congress(i) = Congress(j) Then Votes(i) = Votes(i) + 1
Next j
Next i
For i = 1 To 5
If rVerify < Congress(i) Then rVerify = i
Next i
pVerify = Congress(rVerify)
End Function
Public Property Get GosEgg(Optional ByVal Col As String) As Long
GosEgg = pFinalRow_M1(Col)
End Property
Public Property Get RainMan(Optional ByVal Col As String) As Long
RainMan = pFinalRow_M2(Col)
End Property
'Public Property Get MathIt() As Long
' MathIt = pFinalRow_M3
'End Property
Public Property Get OldTimer() As Long
OldTimer = pFinalRow_M4
End Property
Public Property Get Columbus() As Long
Columbus = pFinalRow_M5
End Property
Public Property Get Slacker(Optional ByVal Col As Long) As Long
Slacker = pFinalRow_M6(Col)
End Property
Private Function pFinalRow(Optional ByVal Col As String, Optional ByVal Min As Boolean) As Long
Dim FinalRow As Long
Select Case Col
Case Is = ""
Select Case Min
Case False
If pFinalRow_M1 > pFinalRow_M2 Then FinalRow = pFinalRow_M1
If pFinalRow_M1 < pFinalRow_M2 Then FinalRow = pFinalRow_M2
'If pFinalRow_M3 > FinalRow Then FinalRow = pFinalRow_M3
If pFinalRow_M5 > FinalRow Then FinalRow = pFinalRow_M5
If pFinalRow_M6 > FinalRow Then FinalRow = pFinalRow_M6
Case True
If pFinalRow_M1 < pFinalRow_M2 Then FinalRow = pFinalRow_M1
If pFinalRow_M1 > pFinalRow_M2 Then FinalRow = pFinalRow_M2
'If pFinalRow_M3 < FinalRow Then FinalRow = pFinalRow_M3
If pFinalRow_M5 < FinalRow Then FinalRow = pFinalRow_M5
If pFinalRow_M6 < FinalRow Then FinalRow = pFinalRow_M6
End Select
Case Is <> 0
Select Case Min
Case False
If pFinalRow_M1(Col) > FinalRow Then FinalRow = pFinalRow_M1(Col)
If pFinalRow_M2(Col) > FinalRow Then FinalRow = pFinalRow_M2(Col)
Case True
If pFinalRow_M1(Col) < FinalRow Then FinalRow = pFinalRow_M1(Col)
If pFinalRow_M2(Col) < FinalRow Then FinalRow = pFinalRow_M2(Col)
End Select
End Select
'If pFinalRow_M4 > FinalRow Then FinalRow = pFinalRow_M4 'Disabled, lags behind.
pFinalRow = FinalRow
End Function
Private Function pFinalRow_M1(Optional ByRef ColLtr As String) As Long
If ColLtr = "" Then ColLtr = "A"
pFinalRow_M1 = Range(ColLtr & "65536").End(xlUp).Row
End Function
Private Function pFinalRow_M2(Optional ByRef Col As String) As Long
Dim i As Byte
Dim FinalRow As Long
Select Case Col
Case Is = ""
For i = 1 To 26
If FinalRow < Cells(ActiveSheet.Rows.Count, i).End(xlUp).Row Then FinalRow = Cells(ActiveSheet.Rows.Count, i).End(xlUp).Row
Next i
Case Is <> ""
FinalRow = Cells(ActiveSheet.Rows.Count, Col).End(xlUp).Row
End Select
pFinalRow_M2 = FinalRow
End Function
Private Function pFinalRow_M3() As Long
Dim FinalRow As Long
Dim ASUC As Long
ASUC = ActiveSheet.UsedRange.Count
FinalRow = ASUC / pFinalRow_M2
pFinalRow_M3 = FinalRow
End Function
Private Function pFinalRow_M4() As Long
'Works on unmodified (saved) sheet only.
Selection.SpecialCells(xlCellTypeLastCell).Select
pFinalRow_M4 = ActiveCell.Row
End Function
Private Function pFinalRow_M5() As Long
On Error GoTo ErrorHandler
'May have problems with hidden rows
'This Method returns 0 on a sheet with no data while the others return 1
pFinalRow_M5 = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
Exit Function
ErrorHandler:
Select Case Err.Number
Case 91
'Assume Error is due to no data, return 0
pFinalRow_M5 = 0
Resume Next
Case Else
On Error GoTo 0
End Select
End Function
Private Function pFinalRow_M6(Optional ByRef ColLtr As Long) As Long
If ColLtr <= 0 Then ColLtr = 1
pFinalRow_M6 = Sheets(ActiveSheet.Name).Cells(Rows.Count, ColLtr).End(xlUp).Row
End Function
Public Function Diagnostics_Run()
Dim FRL As New FinalRowLocator
MsgBox "Columbus: " & FRL.Columbus & Chr(13) _
& "FinalRow: " & FRL.FinalRow & Chr(13) _
& "GosEgg: " & FRL.GosEgg & Chr(13) _
& "OldTimer: " & FRL.OldTimer & Chr(13) _
& "RainMan: " & FRL.RainMan & Chr(13) _
& "Slacker: " & FRL.Slacker '& _
' _ & "MathIt: " & FRL.MathIt & Chr(13)
End Function
Public Property Get DoubleCheck(ByVal Result1 As Long, ByVal Result2 As Long) As Boolean
If Result1 <> Result2 Then DoubleCheck = False
If Result1 = Result2 Then DoubleCheck = True
End Property
Private Property Get pPara()
Dim FRL As New FinalRowLocator
pPara = FRL.FinalRow(, Not FRL.DoubleCheck(FRL.FinalRow, FRL.Verify))
End Property
Public Property Get Para()
Para = pPara
End Property
then:
然后：
Dim FLR as new FinalRowLocator
Dim FinalRow as Long
FinalRow = FRL.FinalRow
Provided you have ascertained the correct finalrow, the following should work.
如果您确定了正确的 finalrow，则以下内容应该有效。
'This will return the column letter
'This Function is dependant on FinalRow returning the correct value.
Dim rInt As Long
rInt = ActiveSheet.UsedRange.Count
psFinalCol = Chr((rInt / FinalRow) + 64)
'This will return the column number
'This Function is dependant on FinalRow returning the correct value.
Dim rInt As Long
rInt = ActiveSheet.UsedRange.Count
piFinalCol = rInt / FinalRow
Additional Usage Information:
其他使用信息：
Dim FRL as New FinalRowLocator 'Create an instance of the FinalRowLocator Class
Dim FinalRow as Long 'Declare the FinalRow Variable as Long
FinalRow = FRL.FinalRow 'Gets the Highest Number returned from all methods
FinalRow = FRL.FinalRow("", true) 'Returns the lowest number from all methods
FinalRow = FLR.FinalRow("A") 'Gets the highest number (column A) returned from methods 1 & 2
FinalRow = FRL.FinalRow("A", true) 'Gets the lowest number (column A) returned from methods 1 & 2
'FRL.DoubleCheck(FRL.FinalRow, FRL.Verify) 'returns true or false based on if the values match
FinalRow.Para is the same as FRL.FinalRow(, Not FRL.DoubleCheck(FRL.FinalRow, FRL.Verify)) 'Returns the lowest row number if the highest one can not be verified.
'FRL.Verify Determins the FinalRow in a Democratic Manner.
FRL.Diagnostics 'will display the results of each individual method in a msgbox
'***** Methods
FRL.Columbus 'Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row 'May have problems with hidden rows 'This Method returns 0 on a sheet with no data while the others return 1
FRL.GosEgg 'does not count past 65536 rows [Range(ColLtr & "65536").End(xlUp).Row]
FRL.OldTimer 'Selection.SpecialCells(xlCellTypeLastCell).Select [Works on Unmodified Saved Sheet Only]
FRL.RainMain 'Cells(ActiveSheet.Rows.Count, Col).End(xlUp).Row
FRL.Slacker 'Sheets(ActiveSheet.Name).Cells(Rows.Count, ColLtr).End(xlUp).Row
