Last column in a particular row

[tiredofit]

I have these functions for finding the last row and columns:

Public Function LRow(ByRef wks As Worksheet) As Long

    On Error GoTo Correction

        With wks
        
            LRow = wks.Cells.Find(What:="*", _
                                  After:=.Cells(.Rows.Count, .Columns.Count), _
                                  SearchDirection:=xlPrevious, _
                                  SearchOrder:=xlByRows).Row
            
        End With
    
Exitpoint:

    On Error GoTo 0
    
    Exit Function
    
Correction:

        LRow = 1

        Resume Exitpoint

End Function

Public Function LRowInCol(ByRef wks As Variant, _
                          ByRef Col As Variant) As Long

    On Error GoTo Correction

        If TypeName(wks) = "String" Then Set wks = Worksheets(wks)

        LRowInCol = wks.Columns(Col).Find(What:="*", _
                                          LookIn:=xlFormulas, _
                                          SearchOrder:=xlRows, _
                                          SearchDirection:=xlPrevious, _
                                          SearchFormat:=False).Row
                                          
Exitpoint:
    
    On Error GoTo 0
    
    Exit Function

Correction:

        LRowInCol = 1
    
        Resume Exitpoint
    
End Function

Public Function LCol(ByRef wks As Worksheet) As Long

    On Error GoTo Correction

        With wks
        
            LCol = .Cells.Find(What:="*", _
                               After:=.Cells(.Rows.Count, .Columns.Count), _
                               SearchDirection:=xlPrevious, _
                               SearchOrder:=xlByColumns).Column
            
        End With
    
Exitpoint:
    
    On Error GoTo 0
    
    Exit Function

Correction:

        LCol = 1
    
        Resume Exitpoint
        
End Function

How can I adapt them to find the last col for a specific row?

Thanks

 

[Joe4]

If you wanted to find the last column with data in, say row 4, you could use this:

lastCol = Cells(4, Columns.Count).End(xlToLeft).Column

 

[tiredofit]

If you wanted to find the last column with data in, say row 4, you could use this:

lastCol = Cells(4, Columns.Count).End(xlToLeft).Column

Thanks, it works for all cases EXCEPT if you have a value in column 16384, ie column XFD.

Can it be corrected to take that into consideration?

 

[tiredofit]

I suppose:

    Dim lastcol As Long
  
    lastcol = Cells(4, Columns.Count).End(xlToLeft).Column

    If Cells(4, 16384).Value <> vbNullString Then
  
        lastcol =16384

    Else
  
        lastcol = Cells(4, Columns.Count).End(xlToLeft).Column

    End If

will do?

 

[Joe4]

Yes, that will work (though I must say, I have never seen anyone ever use the last column before! That's a lot of data!).

By the way, you do not need the first instance of the formula in your code.
Just use:

    Dim lastcol As Long

    If Cells(4, 16384).Value <> vbNullString Then
        lastcol =16384
    Else
        lastcol = Cells(4, Columns.Count).End(xlToLeft).Column
    End If

I typically use <>'' instead of <> vbNullString, but use whatever works for you.

 

[tiredofit]

Yes, that will work (though I must say, I have never seen anyone ever use the last column before! That's a lot of data!).

By the way, you do not need the first instance of the formula in your code.
Just use:

    Dim lastcol As Long

    If Cells(4, 16384).Value <> vbNullString Then
        lastcol =16384
    Else
        lastcol = Cells(4, Columns.Count).End(xlToLeft).Column
    End If

I typically use <>'' instead of <> vbNullString, but use whatever works for you.

Thanks but I'm trying to idiot proof things!

Also given we know the maximum number of columns on a worksheet is "only" 16384, I should've declared lastcol As Integer instead of Long to keep things "as tight as possible".

 

[Joe4]

Also given we know the maximum number of columns on a worksheet is "only" 16384, I should've declared lastcol As Integer instead of Long to keep things "as tight as possible".

I think I saw someone once analyzed it, and found that it did not really save you that much.
The tricky part to remember is that you will need to use "Long" for rows. I typically just use "Long" for everything (and see a lot of other experienced programmers do the same).
Then I help make it "idiot-proof" from myself, too!

 

[tiredofit]

Thanks for your help, have a great evening / morning / afternoon, wherever you are!

 

[Joe4]

You are welcome.
You too!

 

[MARK858]

From MSDN

Traditionally, VBA programmers have used integers to hold small numbers, because they required less memory. In recent versions, however, VBA converts all integer values to type Long, even if they're declared as type Integer. So there's no longer a performance advantage to using Integer variables; in fact, Long variables may be slightly faster because VBA does not have to convert them.