0

I've been trying out various ways of finding the last row in a range and returning this number to a subroutine, to no avail.

Here's the code I have

Sub StartHere()
    Dim oSheet As Worksheet
    Set oSheet = WorkSheets(1)
    ProcessData(oSheet)
End Sub

Sub ProcessData(ByVal wkst As Worksheet)
    Dim rng As Range
    Dim lastRow As Long

    'set range
    Set rng = wkst.Range("L:S") 'Range that i want to process data on

    'get the last row (in Long datatype)
    lastRow = CLng(getLastRowInRange(rng))

End Sub

Function getLastRowInRange(ByRef rng As Range)
    getLastRowInRange = rng.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows,_ SearchDirection:=xlPrevious).Row
End Function

I keep getting Type Mismatch on the 

getLastRowInRange = rng.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows,_ SearchDirection:=xlPrevious).Row

Any clues, guys?

Poliquin
  • 2,937
  • 4
  • 28
  • 32
  • Hi GSerg, no it isn't a duplicate. I had no problems when I tried the getLastRowInRange function by itself, but had problems when i integrated it with StartHere() and ProcessData() subroutines. So I want to figure out what's the problem with my code. – Poliquin Nov 24 '11 at 12:54

1 Answers1

4
ProcessData(oSheet)

Remove the parentheses.

Otherwise you are passing the value of the default property of the oSheet object, which it doesn't have. You should be getting "Object doesn't support this property or method" though.

After doing that, change the function call:

getLastRowInRange = rng.Cells.Find(What:="*", After:=rng.Cells(1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

After must belong to the search range. A1 doesn't belong to L:S.

Finally, make sure you handle the case when Find returns Nothing (which you don't at the moment, happily calling .Row on the returned object).

Community
  • 1
  • 1
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • Hi GSerg, Thanks it works! btw, what does rng.Cells(1) refer to? Also I tried to remove the parentheses but excel 2011 stopped it - error. I put it back on and it works. – Poliquin Nov 24 '11 at 13:39
  • @Yakult121 Are you using Excel for Mac? It may have its differences in regard of syntax. `rng.cells(1)` is the first cell of `rng`. – GSerg Nov 24 '11 at 13:48