如何找到工作簿表的最大行数 - vba

how to find maximum rows of the sheet of workbook - vba

提问by Ashok

I want to get the data from the workbook which contain the multiple sheets and dont know which sheet having the data. So need to check the used rows count of the every sheet of the workbook and activate the sheet which contain maximum used rows for getting the data. struct with below code and any suggestion would appreciate.

我想从包含多张工作表的工作簿中获取数据，但不知道哪张工作表有数据。因此需要检查工作簿的每个工作表的已使用行数并激活包含最大已使用行数的工作表以获取数据。结构与下面的代码和任何建议将不胜感激。

Sub Maxdatasheet()
Dim wscount As Integer
Dim myArray() As Variant

wscount = ActiveWorkbook.Worksheets.Count

  For i = 1 To wscount
    myArray(i) = Worksheets(i).UsedRange.Rows.Count
    Next
    'need to activate the maximus rows of the sheet
End Sub

回答by Sam

A few changes:
- No array, keeping track of what sheet had the maximum instead
- Changed to For Each (even though your code was perfectly fine in that aspect)

一些更改：
- 没有数组，而是跟踪哪个工作表具有最大值
- 更改为 For Each （即使您的代码在这方面非常好）

Sub Maxdatasheet()
    Dim ws As Worksheet
    Dim MaxRowSheet As Worksheet
    Dim MaxRowCount As Long    ' Do not use Integer, may be too small and cause overflow

    wscount = ActiveWorkbook.Worksheets.Count

    MaxRowCount = 0

    For Each ws In ActiveWorkbook.Worksheets
        If ws.UsedRange.Rows.Count > MaxRowCount Then
            MaxRowCount = ws.UsedRange.Rows.Count
            Set MaxRowSheet = ws
        End If
    Next
    MaxRowSheet.Activate
End Sub

回答by Unknown

To get count of used rows from each sheet-

要从每张纸中获取已使用的行数 -

Sub Maxdatasheet()
Dim wscount As Integer
Dim myArray() As Variant

wscount = ActiveWorkbook.Worksheets.Count
ReDim myArray(1 To wscount)

  For i = 1 To wscount
    myArray(i) = Worksheets(i).UsedRange.Rows.Count
    Debug.Print myArray(i)
  Next
End Sub

回答by Frame

Here is a little code I wrote for myself some time ago. It counts all the row numbers of all Worksheets and displays them seperately in a MsgBox (the total sum ist also being displayed).

这是我前段时间为自己写的一小段代码。它计算所有工作表的所有行号并将它们分别显示在 MsgBox 中（总和也被显示）。

Sub Datens?tze_z?hlen()
'
' Datens?tze_z?hlen Makro
' Z?hlt alle Datens?tze, aller Bl?tter einer Excel-Datei und gibt diese in einer MsgBox aus
'

Dim all_rows As Long
Dim sheet_row As String
Dim all_array() As String
Dim max_row As Long

If ActiveWorkbook.Sheets.Count > 1 Then
    ReDim all_array(ActiveWorkbook.Sheets.Count)
    For i = 1 To ActiveWorkbook.Sheets.Count
        ActiveWorkbook.Sheets(i).Activate
        sheet_row = ActiveSheet.name & " " & CStr(ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).row) - 1 ' wenn es keine überschrift gibt, dann ohne "- 1"
        all_rows = all_rows + ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).row - 1 ' wenn es keine überschrift gibt, dann ohne "- 1"
        all_array(i - 1) = sheet_row
    Next i
    MsgBox (Join(all_array, vbCrLf) & vbCrLf & all_rows)
ElseIf ActiveWorkbook.Sheets.Count = 1 Then
    ActiveWorkbook.Sheets(1).Activate
    MsgBox (ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).row) - 1 ' wenn es keine überschrift gibt, dann ohne "- 1"
Else
    MsgBox ("In der ausgew?hlten Excel-Datei ist kein Worksheet vorhanden.")
End If
Erase all_array
End Sub

Hope that that is what you were looking for

希望这就是你要找的

回答by AntiDrondert

Function CountRow(ColumnName As String, ws as Worksheet) As Long
    CountRow = ws.Range(ColumnName& "65536").End(xlUp).row
End Function  

Sub SelectMAX()  
    Dim ws as Worksheet, max as Worksheet
    max = ThisWorkbook.Worksheet(1)
    For Each ws in ThisWorkbook.Worksheets
        if CountRoW("A", ws) > CountRow("A", max) then max = ws
    Next  
    max.Activate
End Sub

Hope this helps.
P.S. You can change "A" to any other column if you need.

希望这可以帮助。
PS 如果需要，您可以将“A”更改为任何其他列。