Finding last row with data working for column A, but not B or C

[Seth Schrock]

I'm having to copy and paste variable amounts (rows) of data into a single column. Thus, I need to know which is the last row that has data in it. I'm using the following function to get me this information:

Public Function LastRow(mySheet As String, myCol As String) As Long
Dim ws As Worksheet
Dim lRow As Long
     
    Set ws = ThisWorkbook.Sheets(mySheet)
         
    With ws
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            LastRow = .Cells.Find(What:="*", _
                          After:=.Range(myCol & "1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        Else
            LastRow = 1
        End If
    End With
    
End Function

Lets say that in column A, I have 48 rows of data (percentages) and in column B I have 16 rows of data (numbers). Column C also has 48 rows of data (again numbers). They are all on sheet "NewSheet". Here are the results that I get when I call this function in the immediate window for each row:

?LastRow("NewSheet", "A")
48
?LastRow("NewSheet", "B")
1
?LastRow("NewSheet", "C")
1

Why does this work for column A, but not for columns B or C?

[NeoPa]

Are you trying to count the number of cells in the column or find the highest row number Seth.
Your code doesn't specify the required column anyway. It simply uses .Cells, which isn't what's required.

[Seth Schrock]

I just copied the code from the internet and then modified it so that I could pass it the sheet and column names. I really don't understand the code, so I'm not sure what is and isn't required.

As for what I'm trying to do... I need to find the last row that has data in it. So if rows 1-16 have data in column B, then I want it to return 16. I suppose a count of the rows with data would also work as there shouldn't ever be any gaps in the data. After re-reading your question, I think that the answer would be to find the highest row number.

I thought I was specifying the required column by passing the A, B or C to myCol which is used in line 10 of the the first code group. However, like I said before, I don't understand this code so I'm not sure exactly what I'm doing. This is probably on the second time I have touched Excel VBA.

[Seth Schrock]

I did find the following code on mrexcel.com

LastRowColA = Range("A65536").End(xlUp).Row

which I modified to

LastRow = Range(myCol & "65536").End(xlUp).Row

and placed it in line 20 of my OP. However, I'm not sure if this is a good method as it comes with the following note: "but this doesn't tell you FOR SURE the last used row in the entire sheet, unless you can be certain that Column A holds the data." I think that I have fixed the problem by passing it the myCol, but I don't know. I also assume that this only works for the active sheet. At this point, I think that this is okay, but I might need to be able to find the last row for a different sheet as I continue with my project.

Here is the link where I found the code: http://www.mrexcel.com/td0058.html

[NeoPa]

Range().SpecialCells(xlLastCell)

This will give you the bottom-right cell used or referenced within your ActiveSheet. The Range() parameters are immaterial. Earlier versions of Excel needed a save to occur before this was ever moved towards A1, though extending the area would always work.

[Seth Schrock]

Can I specify a range? I need the last row for a given column, not the whole worksheet.

[NeoPa]

No. That suggestion was for when the opposite is true. Your modified code in post #4 should give you exactly what you need for that. It wasn't very clear, but I got the impression you wanted something different from that from post #4.

[Seth Schrock]

Okay. I'll just use code then to change the active sheet to the one that I need and not worry about specifying a sheet. Thanks NeoPa.

[Luuk]

Public Function LastRow(mySheet As String, myCol As String) As Long
    
    LastRow = ThisWorkbook.Sheets(mySheet).Range(myCol + "1").End(xlDown).Row
End Function

I dont know why i have 'myCol + "1" ', and in your code i read ' myCol & "1" ', but maybe its just because it's Excel2013