Summary Of Maximum Rows Used Across Each Sheet In A Workbook
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
Code:
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
