Public Function LongestCol(mySheet As String, Optional LongCol As Variant) As Long

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

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