excel - 查找列的最后一行而不包括空白单元格

Question

我的代码有问题。在数据中有 60 行，在 T 列中，有 57 行具有值，即不为空。单元格 58、59、60 为空白。我试图找到 Column T 的最后一行。每当我运行代码时，它总是给我 60 而不是 57。

这是我的代码

Dim lastrow As Long

lastrow = Cells(Rows.Count, 20).End(xlUp).Row

MsgBox "The Last Row of Data for Column T " & lastrow  

Range("B2:T" & lastrow).SpecialCells(xlCellTypeVisible).Select
With Selection.Font
    .Color = -16776961
    .TintAndShade = 0

Answer 1

另一个最后一行问题

简介

• 空单元格就是空单元格。
• 空白单元格可以是空单元格、包含计算结果为 "" 的公式的单元格、包含 ' 的单元格等(如果有的话)。
• 如果要选择非空白单元格，则必须使用 Find 方法的 LookIn 参数的 xlValues 参数.每次使用该方法时都会保存此参数的参数。因此，由于参数设置为 xlValues(这不是默认值)，接受的答案可以正常工作。
• “正确的”数据在底部不包含空白行，因此最常见的方法是使用 LookIn 参数的 xlFormulas 参数。
• 深入研究 Find方法对于揭示无数的可能性至关重要。
• 如果工作表被过滤，Find 方法可能(将)失败，另外当行被隐藏时，xlValues 参数可能(将)失败( xlFormulas 参数将正确处理隐藏的行)。

提示

• This是 Siddharth Rout 对问题Error in finding last used cell in Excel with VBA 的传奇回答的链接，值得(强烈推荐)研究如下。

例子

Option Explicit

' All four solutions assume that there are no hidden or filtered rows.

Sub testLastRowBeginner()
    
    Const FirstRow As Long = 2

    Dim lCell As Range
    Set lCell = Columns(20).Find("*", , xlValues, , , xlPrevious)
    
    ' Assuming that there is data at least in row 2 ('FirstRow') of the column.
    Dim LastRow As Long: LastRow = lCell.Row
    
    MsgBox "The Last Row of Data for Column T " & LastRow
    
    With Range("B" & FirstRow & ":T" & LastRow).SpecialCells(xlCellTypeVisible)
        With .Font
            .Color = -16776961
            .TintAndShade = 0
        End With
    End With
    
End Sub


Sub testLastRowIntermediate()
    
    Const FirstRow As Long = 2

    Dim lCell As Range
    
    ' For non-empty cells (most often):
    'Set lCell = Columns(20).Find("*", , xlFormulas, , , xlPrevious)
    
    ' For non-blank cells which is most probably your case,
    ' because you have formulas evaluting to "":
    Set lCell = Columns(20).Find("*", , xlValues, , , xlPrevious)
    
    Dim LastRow As Long
    If Not lCell Is Nothing Then
        LastRow = lCell.Row
        If LastRow < FirstRow Then
            MsgBox "The last row is smaller then the first.", _
                vbCritical, "Last Row Too Small"
        End If
    Else
        MsgBox "The column range is blank.", vbCritical, "No Last Cell"
        Exit Sub
    End If
    
    MsgBox "The Last Row of Data for Column T " & LastRow
    
    With Range("B" & FirstRow & ":T" & LastRow).SpecialCells(xlCellTypeVisible)
        With .Font
            .Color = -16776961
            .TintAndShade = 0
        End With
    End With
    
End Sub

Sub testLastRowAdvanced()
    
    Const First As String = "T2"

    With Range(First)
        Dim lCell As Range
        Set lCell = .Resize(.Worksheet.Rows.Count - .Row + 1) _
            .Find("*", , xlValues, , , xlPrevious)
        If lCell Is Nothing Then
            MsgBox "No data.", vbExclamation, "Fail"
            Exit Sub
        Else
            LastRow = lCell.Row
        End If
    End With
    
    MsgBox "The Last Row of Data for Column T " & LastRow
    
    With Range("B" & FirstRow & ":T" & LastRow).SpecialCells(xlCellTypeVisible)
        With .Font
            .Color = -16776961
            .TintAndShade = 0
        End With
    End With
    
End Sub

Sub testLastRowExpert()
    
    Const Cols As String = "B:T"
    Const FirstRow As Long = 2

    Dim rg As Range
    With Columns(Cols).Rows(FirstRow)
        Dim lCell As Range
        Set lCell = .Resize(.Worksheet.Rows.Count - FirstRow + 1) _
            .Find("*", , xlValues, , xlByRows, xlPrevious)
        If lCell Is Nothing Then
            'MsgBox "No data.", vbExclamation, "Failure"
            Exit Sub
        End If
        Set rg = .Resize(lCell.Row - .Row + 1)
    End With
    'Debug.Print rg.Address
    
    With rg.SpecialCells(xlCellTypeVisible)
        With .Font
            .Color = -16776961
            .TintAndShade = 0
        End With
    End With
    
End Sub