Rows.count错误 [英] Rows.count incorrect

问题描述

我有: N = Cells(Rows.Count，"B").End(xlUp).Row 当我有一个包含200k +行的数据集时，它总是一直返回1.

I have: N = Cells(Rows.Count, "B").End(xlUp).Row and it returns 1 all the time, when I have a dataset which contains 200k+ rows.

很奇怪，这只适用于此工作簿，此行也适用于另一工作簿.

Very odd, as it is only with this workbook, as with another workbook this line works.

问题:

关于为什么会发生这种情况的任何建议?有任何可能的解决方法吗?我目前的解决方法是:

Any suggestions as to why this occurs? Any possible work around? My current work around is:

Sub Macro2()

    Range("B1").Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlUp).Select
    n = ActiveCell.Row
End Sub

推荐答案

示例

Option Explicit
Private Sub Example()
    With ThisWorkbook.Worksheets(1)
        Dim LAST_ROW As Long
            LAST_ROW = .Range("A" & .Rows.Count).End(xlUp).Row

        Debug.Print LAST_ROW ' Print on Immediate Window
    End With
End Sub

找到最后一行的多种方式

Private Sub Example2()
    Dim Sht As Worksheet
    Set Sht = ActiveWorkbook.Sheets(1)

    Dim LAST_ROW As Long

    'Using Find
      LAST_ROW = Sht.Cells.Find("*", searchorder:=xlByRows, _
                                     searchdirection:=xlPrevious).Row

    'Using SpecialCells
      LAST_ROW = Sht.Cells.SpecialCells(xlCellTypeLastCell).Row

    'Ctrl + Shift + End
      LAST_ROW = Sht.Cells(Sht.Rows.Count, "A").End(xlUp).Row

    'UsedRange
      LAST_ROW = Sht.UsedRange.Rows(Sht.UsedRange.Rows.Count).Row

    'Using Named Range
      LAST_ROW = Sht.Range("MyNamedRange").Rows.Count

    'Ctrl + Shift + Down
      LAST_ROW = Sht.Range("A1").CurrentRegion.Rows.Count

End Sub

这篇关于Rows.count错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！