UDF to convert column Letter to Number and return the Largest column as Number

[10shlomi10]

Hi

I need help with UDF please.
I have the a cells range with columns letters.

I want to build a UDF that will return the highest column latter BUT as A Number (what is called: Excel column number index).

I start writing something but I'm stack ...

Public Function MaxColNum(ByRef rng As Range, ColNm As Variant)'convert column Letter to Number and find the Largest column
   LastCol = ActiveSheet.Cells("A", Columns.Count).End(xlToLeft).Column
   lastColLetter = Range(LastCol, LastCol & 1).Column
   
   Set rng = Range("C2:" & lastColLetter & Rows.Count)
    
    For Each ColNm In rng
        MaxColNum = Range(ColNm, ColNm & 1).Column
    Next
        
        'MaxwColNm = ......


End Function

C2 is the first cell to search till the end of rows+end of columns

2016-12-24_02-59-13.png

Thanks !

[SamT]

If ColNm > BiggestLetter Then BiggestLetter = ColNm
Next ColNm
MaxColNum = Cells(1, BiggestLetter).Column

[10shlomi10]

If ColNm > BiggestLetter Then BiggestLetter = ColNm
Next ColNm
MaxColNum = Cells(1, BiggestLetter).Column

Thanks!
but....
From where the variable BiggestLetter came from ? and How the code can know what is the BiggestLetter is ? as this is the exactly thing (the Largest Column Letter) I want to find....

appreciate your explanation.

[10shlomi10]

Got it work.

Here's the code:

Sub MaxColNum()Dim rng As Range
'convert column Letter to Number and find the Largest column
    Dim LastColumn As Long
    Dim LastRow As Long
    
    With ActiveSheet.UsedRange
        LastColumn = .Columns(.Columns.Count).Column
        LastRow = .Rows(.Rows.Count).row
   End With
   
    'Clear the 'cell Named MaxLC. in the end of the the BIGGEST LETTER will be placed on this cell 
    Range("MaxLC") = ""
   
  'set the Range to Loop 
   Set rng = Range(Cells(2, 3), Cells(LastRow, LastColumn))
    
    BiggestLetter = 0


    For Each ColN In rng
        If ColN <> "" Then
            'Convert Column Letter into column Number
            'and load the number into ColN variable
            ColN = Range(ColN & 1).Column
        If ColN > BiggestLetter Then BiggestLetter = ColN
        End If
    Next ColN


    'Placed the BiggestLetter found into cell Named: MaxLC
    Range("MaxLC") = BiggestLetter


End Sub

[SamT]