vba 获取实际使用范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7423022/
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
Getting the actual usedrange
提问by thinkanotherone
I have a Excel worksheet that has a button.
我有一个带有按钮的 Excel 工作表。
When I call the usedRange() function, the range it returns includes the button part.
当我调用 usedRange() 函数时，它返回的范围包括按钮部分。
Is there anyway I can just get actual used range that contains data?
无论如何我可以得到包含数据的实际使用范围吗？
回答by Reafidy
What sort of button, neither a Forms Control nor an ActiveX control should affect the used range.
什么样的按钮，Forms 控件和 ActiveX 控件都不应该影响使用的范围。
It is a known problem that excel does not keep track of the used range very well. Any reference to the used range via VBA will reset the value to the current used range. So try running this sub procedure:
excel 不能很好地跟踪使用的范围是一个已知的问题。通过 VBA 对使用范围的任何引用都会将该值重置为当前使用的范围。所以尝试运行这个子过程：
Sub ResetUsedRng()
Application.ActiveSheet.UsedRange
End Sub
Failing that you may well have some formatting hanging round. Try clearing/deleting all the cells after your last row.
否则，您可能会有一些格式悬而未决。尝试清除/删除最后一行之后的所有单元格。
Regarding the above also see:
关于以上内容，另见：
Another method to find the last used cell:
查找最后使用的单元格的另一种方法：
Dim rLastCell As Range
Set rLastCell = ActiveSheet.Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
Change the search direction to find the first used cell.
更改搜索方向以查找第一个使用的单元格。
回答by JMax
Readify made a very complete answer. Yet, I wanted to add the Endstatement, you can use:
Readify做了一个非常完整的回答。但是，我想添加End语句，您可以使用：
Find the last used cell, before a blank in a Column:
找到最后使用的单元格，在列中的空白之前：
Sub LastCellBeforeBlankInColumn()
Range("A1").End(xldown).Select
End Sub
Find the very last used cell in a Column:
查找列中最后使用的单元格：
Sub LastCellInColumn()
Range("A" & Rows.Count).End(xlup).Select
End Sub
Find the last cell, before a blank in a Row:
找到最后一个单元格，在一行空白之前：
Sub LastCellBeforeBlankInRow()
Range("A1").End(xlToRight).Select
End Sub
Find the very last used cell in a Row:
找到一行中最后使用的单元格：
Sub LastCellInRow()
Range("IV1").End(xlToLeft).Select
End Sub
See herefor more information (and the explanation why xlCellTypeLastCellis not very reliable).
有关更多信息（以及为什么不是很可靠的解释），请参见此处xlCellTypeLastCell。
回答by davidlandy
Here's a pair of functions to return the last row and col of a worksheet, based on Reafidy's solution above.
这是基于 Reafidy 上面的解决方案的一对函数，用于返回工作表的最后一行和列。
Function LastRow(ws As Object) As Long
Dim rLastCell As Object
On Error GoTo ErrHan
Set rLastCell = ws.Cells.Find("*", ws.Cells(1, 1), , , xlByRows, _
xlPrevious)
LastRow = rLastCell.Row
ErrExit:
Exit Function
ErrHan:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbExclamation, "LastRow()"
Resume ErrExit
End Function
Function LastCol(ws As Object) As Long
Dim rLastCell As Object
On Error GoTo ErrHan
Set rLastCell = ws.Cells.Find("*", ws.Cells(1, 1), , , xlByColumns, _
xlPrevious)
LastCol = rLastCell.Column
ErrExit:
Exit Function
ErrHan:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbExclamation, "LastRow()"
Resume ErrExit
End Function
回答by David F Mayer
Public Sub FindTrueUsedRange(RowLast As Long, ColLast As Long)
Application.EnableEvents = False
Application.ScreenUpdating = False
RowLast = 0
ColLast = 0
ActiveSheet.UsedRange.Select
Cells(1, 1).Activate
Selection.End(xlDown).Select
Selection.End(xlDown).Select
On Error GoTo -1: On Error GoTo Quit
Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Activate
On Error GoTo -1: On Error GoTo 0
RowLast = Selection.Row
Cells(1, 1).Activate
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Activate
ColLast = Selection.Column
Quit:
Application.ScreenUpdating = True
Application.EnableEvents = True
On Error GoTo -1: On Error GoTo 0
End Sub
回答by Bill Hudson
This function returns the actual used range to the lower right limit. It returns "Nothing" if the sheet is empty.
该函数返回实际使用的范围到右下限。如果工作表为空，则返回“Nothing”。
'2020-01-26
Function fUsedRange() As Range
Dim lngLastRow As Long
Dim lngLastCol As Long
Dim rngLastCell As Range
On Error Resume Next
Set rngLastCell = ActiveSheet.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious)
If rngLastCell Is Nothing Then 'look for data backwards in rows
Set fUsedRange = Nothing
Exit Function
Else
lngLastRow = rngLastCell.Row
End If
Set rngLastCell = ActiveSheet.Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious)
If rngLastCell Is Nothing Then 'look for data backwards in columns
Set fUsedRange = Nothing
Exit Function
Else
lngLastCol = rngLastCell.Column
End If
Set fUsedRange = ActiveSheet.Range(Cells(1, 1), Cells(lngLastRow, lngLastCol)) 'set up range
End Function
回答by swhgraham
I use the following vba code to determine the entire used rows range for the worksheet to then shorten the selected range of a column:
我使用以下 vba 代码来确定工作表的整个使用行范围，然后缩短列的选定范围：
Set rUsedRowRange = Selection.Worksheet.UsedRange.Columns( _
Selection.Column - Selection.Worksheet.UsedRange.Column + 1)
Also works the other way around:
也可以反过来工作：
Set rUsedColumnRange = Selection.Worksheet.UsedRange.Rows( _
Selection.Row - Selection.Worksheet.UsedRange.Row + 1)
回答by Aditya Gupta
This function gives all 4 limits of the used range:
此函数给出了使用范围的所有 4 个限制：
Function FindUsedRangeLimits()
Set Sheet = ActiveSheet
Sheet.UsedRange.Select
' Display the range's rows and columns.
row_min = Sheet.UsedRange.Row
row_max = row_min + Sheet.UsedRange.Rows.Count - 1
col_min = Sheet.UsedRange.Column
col_max = col_min + Sheet.UsedRange.Columns.Count - 1
MsgBox "Rows " & row_min & " - " & row_max & vbCrLf & _
"Columns: " & col_min & " - " & col_max
LastCellBeforeBlankInColumn = True
End Function
