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

Question

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

Answer 1

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)

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

Answer 2

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