Public Function LongestRow(mySheet As String, Optional LongRow As Variant) As Long

'   This function will return a value that is the column number of the column with the most rows.
'   The only parameter needed is the name of the sheet. An optional parameter identifies the number
'   of the rows in that column. If several columns have the same number of rows, the first column with that
'   number of rows is returned.

myLastRow = ThisWorkbook.Sheets(mySheet).Cells(Rows.Count, 1).End(xlUp).Row
Dim c As Long, ret As Long
c = 0
ret = 0
For X = 1 To myLastRow
    If c < ThisWorkbook.Sheets(mySheet).Cells(X, Columns.Count).End(xlToLeft).Column Then
        c = ThisWorkbook.Sheets(mySheet).Cells(X, Columns.Count).End(xlToLeft).Column
        ret = X
    End If
Next
LongestRow = ret
If Not IsMissing(LongRow) Then
    LongRow = c
End If
End Function