Summary Of Maximum Rows Used Across Each Sheet In A Workbook

[Excel Fox]

So this little routine basically gives a raw aka primitive indication of what are the maximum rows used across each sheet. Just wrote this for something I needed urgently, so the code would be quick and dirty. Due credit to shg for the letter converter

Sub SheetUsageByColumn()

    Dim wks As Worksheet
    Dim lngCount As Long
    Dim lngCol As Long
    Dim lngRow As Long
    Dim lngMaxRow As Long
    Dim lngMaxCol As Long
    Dim lngStartRow As Long
    Dim lngStartCol As Long
    Dim strMessage As String
    Dim lngTotalRows As Long: lngTotalRows = ActiveWorkbook.Sheets(1).Rows.Count
    
    For Each wks In ActiveWorkbook.Worksheets
        strMessage = strMessage & "For '" & wks.Name & "' tab with |<>|" & vbLf & vbLf
        lngStartRow = wks.Cells(wks.Rows.Count, 1).End(xlUp).Row
        lngStartCol = 1
        For lngCol = 2 To wks.UsedRange.Columns.Count
            lngRow = wks.Cells(lngTotalRows, lngCol).End(xlUp).Row
            If lngRow <> lngStartRow Then
                lngMaxRow = Application.Max(lngMaxRow, lngRow, lngStartRow)
                strMessage = strMessage & ColLtr(lngStartCol) & "-" & ColLtr(lngCol - 1) & ":" & lngStartRow & vbTab
                lngStartRow = lngRow
                lngStartCol = lngCol
            End If
        Next lngCol
        lngMaxCol = lngCol - 1
        strMessage = Replace(strMessage, "|<>|", "upto " & lngMaxRow & " rows in " & lngMaxCol & " columns.")
        strMessage = strMessage & vbLf & vbLf
        lngMaxRow = Empty
    Next wks
    MsgBox strMessage
    
End Sub


Function ColLtr(ByVal iCol As Long) As String
    ' shg 2012
    If iCol Then ColLtr = ColLtr((iCol - 1) \ 26) & Chr(65 + (iCol - 1) Mod 26)
    
End Function