logo标签列表

在VBA中查找Excel工作表的底部

excelvba
5
我想选择工作表的底部，但不包括未使用/存储的部分。我可能有10,000行，但肯定没有65,536行。我事先不知道有多少行。
在Excel本身中（至少在最近的版本中；Excel 97不太友好），您可以按Ctrl + End键跳转到最后一行和列。我想要相同的功能。
7个回答
4
最简单的方法是从底部开始，逐行向上查找，找到最后一个包含内容的行：
Range("A65536").end(xlup).row
除非工作表填满了第65536行，而在99%的情况下，这种情况并不会发生。否则，我会使用range("A1").end(xlDown)或类似方法。 - Ravindra Sane
3
如果您向下滚动表格，当遇到第一个空单元格时，Excel 会停止滚动：而通常情况下，空单元格后面还有更多数据。如果您从底部开始向上滚动，则不会遇到这个问题。 - Charles Williams
3

那很基础：

Selection.End(xlDown).Select

（通过录制宏时按下ctrl + end键发现的。）
你必须小心，如果有一个空行，有时它只会选择到该行并忽略之后的所有内容。通常我会获取它所在的行，检查接下来的2-3个单元格，如果没有找到匹配项，则接受它，否则我会找到该区域的“结尾”，并循环直到我有2或3个连续的空白。 - pinkfloydx33
2
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 = "$F$1105"

Range(myRange).Select
Application.ScreenUpdating = True

End Sub' End of Macro Code
1
0

我之前用过这个

'This finds the last row in thr worksheet
LR = Cells(Rows.Count, 18).End(xlUp).Row
Range(Cells(LR, 1), Cells(LR, 35)).Select
0

此函数返回具有某些内容的工作表中最大行数和最大列数。也许对某些人有用。当然，这很慢，但通常我们不必检查所有行和列，因此需要调整循环。

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
0

有很多方法可以做到这一点，

有些是不正确的，

End(xlDown)和SpecialCells(xlCellTypeLastCell)等等...都可能出现问题，

它们可能会跳过一些因为它们被隐藏或其他原因而被忽略的空格。

我更喜欢使用.find来查找这样的东西，只是为了确保

最简单的方法：

Dim LastCell   As Range
Set LastCell = Cells(Rows.Count, Columns.Count)

MsgBox LastCell.Address

'（基本上计算行和列的数量，并使用这些数字获取最后位置）

'我的主要方法：

Dim FinalCell as range
Set FinalCell = Cells.Find(What:="", _
                After:=Range("a1"), _
                LookAt:=xlPart, _
                LookIn:=xlFormulas, _
                searchorder:=xlByColumns, _
                searchdirection:=xlPrevious, _
                MatchCase:=False)
msgbox FinalCell.Address

'（它从单元格a1开始， '向后移动，

'因为左侧或上方没有空间， '所以它会到达工作表的末尾）

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接