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

 

[tiredofit]

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

Try putting values in E1048675 and H1048676.

It returns 5, not 8 for me.


​

 

[JoeMo]

Try putting values in E1048675 and H1048676.

It returns 5, not 8 for me.


​

Returns 8 for me.

 

[tiredofit]

Returns 8 for me.

I have Excel 2013, 32 bit.

What's yours?

 

[tiredofit]

Returns 8 for me.

OK, if you have the time, please replicate these steps:

I copied the function onto notepad, opened a new instance of Excel (2013, 32 bit), inserted a new module, pasted the function into the module, added a value of 1 in cell E1048575 and cell H1048576.

Went to the immediate window and typed:

? lcol(sheet1)

and returned 5.


​

 

[JoeMo]

I use Excel 2010, 32 bit, but don't believe it's a version issue. Did you put the 1 values in Sheet1?

 

[tiredofit]

I use Excel 2010, 32 bit, but don't believe it's a version issue. Did you put the 1 values in Sheet1?

Yes I did.

I agree with you it did work after a while buy it systematically failed the first time, every time.

Not sure what that's the case.

 

[tiredofit]

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("*", , xlValues, , 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,

Correct me if I'm wrong but I used your function in my program and it returned an error.

Further investigation led me to this:

In a workbook with 2 worksheets, put a value in cell E100 in sheet1.

Go to sheet2.

Now run this code:

Option Explicit

Sub Test()


Dim a As Long

a = LRowInCol(Sheet1, 5)

End Sub


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​

I expected the code to return a value of 100. Instead it returned 0.

I think the problem is that Sheet1 was not the active sheet. Your function works only if you are on the active sheet.

I think it ought to be:

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​

ie a . before the Column.