Last column in a particular row

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,404
I have these functions for finding the last row and columns:

Code:
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,717
Office Version
  1. 365
Platform
  1. Windows
If you wanted to find the last column with data in, say row 4, you could use this:
VBA Code:
lastCol = Cells(4, Columns.Count).End(xlToLeft).Column
 

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,404
If you wanted to find the last column with data in, say row 4, you could use this:
VBA Code:
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

Well-known Member
Joined
Apr 11, 2013
Messages
1,404
I suppose:

Code:
    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?
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,717
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:
VBA Code:
    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.
 
Solution

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,404
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:
VBA Code:
    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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,717
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Apr 11, 2013
Messages
1,404
Thanks for your help, have a great evening / morning / afternoon, wherever you are!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,717
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
You too! :)
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,005
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,015
Messages
5,628,147
Members
416,295
Latest member
jjkh58

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top