Find last row in a data table on a different worksheet then the active one.

[Kryst51]

I have a data table on one sheet, and a pivot on the next.

When I click something on the pivot table I want each recrod in the data table to be examined for a matching number. I have this portion figured out, what I can't figure out is how to find the last row of the data table for my for...next loop. Anything I find will only find the last row of the active sheet.

Any help is appreciated

Here is my function for finding the last row:

Function LastRow(AcctNumber As Integer) As Integer
'--------------------------------------------
' Procedure : LastRow
' Author    : kthompson
' Date      : 12/21/2010
'
'
'--------------------------------------------
On Error GoTo LastRow_Error
Dim LRow As Integer
 
[COLOR=green]'This is where I don't know how to make it look at the data sheet[/COLOR]
[COLOR=green]'instead of the pivot table sheet. The with part doesn't work[/COLOR]
With Worksheets("2010 DATA")
 
LRow = Cells.Find("*", SearchOrder:=xlByRows,  SearchDirection:=xlPrevious).Row
 
End With
 
   Dim i As Integer
    For i = 2 To LRow - 1
If Sheets("2010 DATA").Range("D" & i).Value = AcctNumber Then
Sheets("2010 DATA").Range("K" & i) = Sheets("2010 TABLE").Range("D2")
End If
    Next i
 
LastRow_Exit:
    Exit Function
LastRow_Error:
MsgBox (Err.Number & ", " & Err.Description & ", Record Number: " & i)
    Resume LastRow_Exit
 
End Function

 

[Kryst51]

WooHoo! I figured it out... My logic got mixed up... Silly With block doesn't need to be there.... Just this:

Function LastRow(AcctNumber As Integer) As Integer
'--------------------------------------------
' Procedure : LastRow
' Author    : kthompson
' Date      : 12/21/2010
'
'
'--------------------------------------------
On Error GoTo LastRow_Error
Dim LRow As Integer
 
[COLOR=red][B][U]LRow = Worksheets("2010 DATA").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row[/U][/B][/COLOR]
   Dim i As Integer
    For i = 2 To LRow - 1
If Sheets("2010 DATA").Range("D" & i).Value = AcctNumber Then
Sheets("2010 DATA").Range("K" & i) = Sheets("2010 TABLE").Range("D2")
End If
    Next i

LastRow_Exit:
    Exit Function
LastRow_Error:
MsgBox (Err.Number & ", " & Err.Description & ", Record Number: " & i)
    Resume LastRow_Exit
    
End Function

I could have sworn that I had tried something like it already, but then in thinking about how to do it best, I got mixed up

 

[Banana]

FWIW: There's also a UsedRange which is very useful in telling you the boundary of where there are content and thus can be used to identify the last row/column that's actually used.

 

[chergh]

Or you could do it like this:

Public Function RHLastRow(ws As Worksheet) As Long

    RHLastRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row

End Function

Seasons greetings