在 VBA 中查找 Excel 工作表的底部
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4092329/
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 bottom of Excel worksheet in VBA
提问by Charles
I'd like to select to the bottom of the worksheet, but not below what is used/stored. I might have 10,000 rows, but I certainly don't have 65,536. I won't know ahead of time how many rows.
我想选择到工作表的底部，但不低于使用/存储的内容。我可能有 10,000 行，但我当然没有 65,536。我不会提前知道有多少行。
In Excel itself (in recent versions, anyway; Excel 97 wasn't so kind) you can press Ctrl + End to be taken to the last row and column. I'd like the same functionality.
在 Excel 本身中（无论如何，在最近的版本中；Excel 97 不是那么友好）您可以按 Ctrl + End 转到最后一行和最后一列。我想要同样的功能。
采纳答案by Charles Williams
The simplest way is to start at the bottom and work up to find the last row that contains something:
Range("a65536").end(xlup).row
最简单的方法是从底部开始，向上查找包含某些内容的最后一行：
Range("a65536").end(xlup).row
回答by steinar
That's elementary:
这是基本的：
Selection.End(xlDown).Select
(Found this out by pressing ctrl + end while recording a macro.)
（通过在录制宏时按 ctrl + end 发现了这一点。）
回答by Jolly Chollie
Public Sub Blank_Row_Remover() ' Start of Macro Code
'Deletes the Entire Row within the Selection if _
Some of the Cells Within the Selection Contain No Data.
Dim Start_Cell, End_Cell, Data_Info, End_Column, This_Column As Variant
Application.ScreenUpdating = False
Application.StatusBar = "Please Stand By, ('Removing Blank Rows...' ~ Macro In Progress)..."
Call Data_Info_Selection(Start_Cell, End_Cell, Data_Info, End_Column, This_Column) ' Direct Method
For Each Cell In Selection
Cell.Formula = Replace(Cell.Formula, Cell.Formula, Trim(Cell.Formula)) {Rids Extra Spaces}
'If InStr(Cell.Value, "Labels:") Then Cell.EntireRow.Clear 'Searching for a Particular String to Remove a Row
'If InStr(Cell.Value, " ") Then Cell.EntireRow.Clear 'Searching for another Particular String to Remove a Row {Like 4 Spaces in a Cell that Keeps it from Reading as a Blank}
Next
On Error Resume Next
Selection.SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
'Call Data_Info_Selection(Start_Cell, End_Cell, Data_Info, End_Column, This_Column) ' Direct Method
Application.ScreenUpdating = True
End Sub
Public Function Data_Info_Selection(ByRef Start_Cell, End_Cell, Data_Info, End_Column, This_Column As Variant)
Application.ScreenUpdating = False
Application.StatusBar = "Please Stand By, ('Selecting Partial Columns' ~ Macro In Progress)..."
Start_Cell = ActiveCell.Address
Start_Cell_Text = Range(Start_Cell).Text
Orginal_Start_Cell = Range(Start_Cell).Address
If Start_Cell_Text = "" Then
Dim Cells As Range
For Each Cell In Selection.Cells
If Cell = "" Then
Start_Cell = Cell.Address
Else
Start_Cell = Cell.Address
Exit For
End If
Next
End If
This_Column = Mid(Start_Cell, 2, 1) 'ColumnNum = ActiveCell.Column
If Range(Start_Cell).Text = "" Then
End_Column = This_Column & ActiveCell.Row
End_Cell = Range(End_Column).Address
Else
End_Column = This_Column + "65536"
End_Cell = Range(End_Column).End(xlUp).Address
Start_Cell = Range(Orginal_Start_Cell).Address
End If
Data_Info = Range(Start_Cell, End_Cell).Address
Range(Data_Info).Select
End Function
Public Sub Select_Partial_Data_Start_Cell() ' (This Seems to Work and is Cleaner and Simplier)
Application.ScreenUpdating = False
Application.StatusBar = "Please Stand By, ('Selecting Partial Data' ~ Macro In Progress)..."
Dim myLastRow As Long
Dim myLastColumn As Long
Start_Cell = ActiveCell.Address
On Error Resume Next
myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
myLastColumn = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
myLast_Cell = Cells(myLastRow, myLastColumn).Address
myRange = Start_Cell & ":" & myLast_Cell
'If InStr(1, myLast_Cell, "104876", 1) Then myLast_Cell = "$F05"
Range(myRange).Select
Application.ScreenUpdating = True
End Sub' End of Macro Code
回答by Fionnuala
It would be safer to assume gaps and use something from XL: How to Determine Top/Bottom Used Cells in a Sparse Array
假设间隙并使用XL 中的一些东西会更安全：如何确定稀疏数组中的顶部/底部使用的单元格
回答by TkdKidSnake
I've used this previously
我以前用过这个
'This finds the last row in thr worksheet
LR = Cells(Rows.Count, 18).End(xlUp).Row
Range(Cells(LR, 1), Cells(LR, 35)).Select
回答by kamila
This function returns no of max row and max column in the worksheet with some content. maybe it will be usefull for sbdy. of course this is very slow, but usually we don't have to check all the rows and columns so one have to adjust the loops.
此函数返回工作表中包含某些内容的最大行数和最大列数。也许它对 sbdy 有用。当然这很慢，但通常我们不必检查所有行和列，因此必须调整循环。
Public Function content_area(shName As String) As Variant
Dim res(1 To 2) As Integer
Dim ark As Worksheet
Set ark = ThisWorkbook.Sheets(shName)
nCol = 0
nRow = 0
For i = 1 To ark.Columns.Count
temp = ark.Cells(ark.Cells(1, i).EntireColumn.Rows.Count, i).End(xlUp).Row
If temp > nCol Then nCol = temp
Next
For i = 1 To ark.Rows.Count
temp = ark.Cells(i, ark.Cells(i, 1).EntireRow.Columns.Count).End(xlToLeft).Column
If temp > nRow Then nRow = temp
Next
res(1) = nCol
res(2) = nRow
content_area = res
End Function
