Last row in a column

[tiredofit]

I have this function which calculates the last row in a column of a particular worksheet:

Public Function LRowInCol(ByRef wks As Worksheet, ByRef Col As Integer) As Long
    
    With wks
    
        LRowInCol = .Cells(.Rows.Count, Col).End(xlUp).Row
    
    End With
    
End Function

so if I put a value in cell E10 and then write in the immediate window:

? LRowInCol(Sheet1, 5)

I get 10 returned, which is correct.

Assume now I put a value in cell E1048676, ie the last row in column E.

This still returns 10:

? LRowInCol(Sheet1, 5)

it's as if it ignores the "real" last row and returns the next one, which is the row of the value in cell E10.

What is wrong with function?

Thanks

 

[RoryA]

The code is the equivalent of selecting the last cell in the column and then pressing Ctrl+up arrow. It never actually checks to see if the last cell in the column is populated.

 

[wideboydixon]

That's the corner case that's not covered. You could do this:

Public Function LRowInCol(ByRef wks As Worksheet, ByRef Col As Integer) As Long
    
    With wks
        If .Cells(.Rows.Count, Col).Value <> "" Then
            LRowInCol = .Rows.Count
        Else
            LRowInCol = .Cells(.Rows.Count, Col).End(xlUp).Row
        End If
    End With
    
End Function

WBD

 

[JoeMo]

Most of us never encounter a situation where the last row of a post-xl2003 worksheet is used so we find the last used row in the way your function does. But for completeness to include the possibility that the real last row is the last row on the sheet you can write your function like this:

Public Function LRowInCol(ByRef wks As Worksheet, ByRef Col As Integer) As Long
    With wks
        If IsEmpty(.Cells(.Rows.Count, Col)) Then
            LRowInCol = .Cells(.Rows.Count, Col).End(xlUp).Row
        Else
            LRowInCol = .Rows.Count
        End If
    End With
End Function

 

[Rick Rothstein]

In addition to the code JoeMo posted, here is another way to write your function...

[table="width: 500"]
[tr]
	[td]Public Function LRowInCol(ByRef wks As Variant, ByRef Col As Variant) As Long
  On Error GoTo NothingInColumn
  If TypeName(wks) = "String" Then Set wks = Sheets(wks)
  With wks
    LRowInCol = Columns(Col).Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row
  End With
NothingInColumn:
End Function
[/td]
[/tr]
[/table]

Three things to note with my function...

1) You can specify the worksheet as a worksheet object or by its name as a quoted string

2) You can specify the Col by its number or its letter designation.

3) If the column has no values in any of its cells, the function returns 0.

 

[tiredofit]

Thanks to all for your suggestions.

 

[tiredofit]

On a similar note, I have this function to return the last row and it DOES work:

Public Function LRow(ByRef wks As Worksheet) As Long
    On Error GoTo Correction
        With wks
        
            LRow = .Cells.Find(What:="*", _
                               After:=.Cells(Rows.Count, Columns.Count), _
                               SearchDirection:=xlPrevious, _
                               SearchOrder:=xlByRows).Row
            
        End With
    
        GoTo Exitpoint
    
Correction:
        LRow = 1
Exitpoint:
    On Error GoTo 0
End Function

so I adapted it for columns as follows but it fails:

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
    
        GoTo Exitpoint
    
Correction:
        LCol = 1
Exitpoint:
    On Error GoTo 0
End Function

in that if I put a value in cell E1048675, it returns 5 (ie column E) but if there's also a value in cell H1048676, it still returns 5.

Given the LRow function works, why does this fail?


​

 

[JoeMo]

In addition to the code JoeMo posted, here is another way to write your function...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Public Function LRowInCol(ByRef wks As Variant, ByRef Col As Variant) As Long
  On Error GoTo NothingInColumn
  If TypeName(wks) = "String" Then Set wks = Sheets(wks)
  With wks
    LRowInCol = Columns(Col).Find("*", , [COLOR=#ff0000][B]xlValues[/B][/COLOR], , xlRows, xlPrevious, , , False).Row
  End With
NothingInColumn:
End Function
[/TD]
[/TR]
</tbody>[/TABLE]

Three things to note with my function...

1) You can specify the worksheet as a worksheet object or by its name as a quoted string

2) You can specify the Col by its number or its letter designation.

3) If the column has no values in any of its cells, the function returns 0.

Rick, your function will not pick up a formula in the last row that returns "". That can be fixed by changing xlValues to xlFormulas.

 

[Rick Rothstein]

Rick, your function will not pick up a formula in the last row that returns "". That can be fixed by changing xlValues to xlFormulas.

I copied/modified the wrong code line from my storehouse of routines I have posted in the past.

Thanks for catching that.

 

[JoeMo]

On a similar note, I have this function to return the last row and it DOES work:

Public Function LRow(ByRef wks As Worksheet) As Long
    On Error GoTo Correction
        With wks
        
            LRow = .Cells.Find(What:="*", _
                               After:=.Cells(Rows.Count, Columns.Count), _
                               SearchDirection:=xlPrevious, _
                               SearchOrder:=xlByRows).Row
            
        End With
    
        GoTo Exitpoint
    
Correction:
        LRow = 1
Exitpoint:
    On Error GoTo 0
End Function

so I adapted it for columns as follows but it fails:

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
    
        GoTo Exitpoint
    
Correction:
        LCol = 1
Exitpoint:
    On Error GoTo 0
End Function

in that if I put a value in cell E1048675, it returns 5 (ie column E) but if there's also a value in cell H1048676, it still returns 5.

Given the LRow function works, why does this fail?


​

Works fine for me when there's something in E1048675 and H1048675, returning 8.