CODEWITHSUNDEEP
excelvba
Tourless
Tourless

Reputation: 27

Sheet name as variable to get last row from function

Can I pass a sheet name as a variable to a function to return the last row of the given sheet? If so, what would the structure of the code look like? How would I pass the variable? I'm using a simple function

Function lastRow()

    With ActiveSheet
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

End Function

and calling it here

Sub CopyResults()

'Copy DP-CustomerDates
DPCustomerDates.Range("A1:D" & lastRow).Copy

I find the last row multiple times on multiple sheets and origirnally created the function to avoid having the retype the whole with block over and over. I'm revising my code base on some great information I've gotten from here and codereview and I'm stuck on this new question.

Upvotes: 0

Views: 496

Answers (1)

Scott Craner
Scott Craner

Reputation: 152485

Pass the worsheet as a parameter:

Function lastrow(ws As Worksheet) As Long

    With ws
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

End Function

Then call it thus:

Sub CopyResults()

'Copy DP-CustomerDates
DPCustomerDates.Range("A1:D" & lastrow(DPCustomerDates)).Copy
End Sub

But if you are going to use that same number multiple times, instead of doing multiple calls, assign the return to a variable and call it only once.

Sub CopyResults()
Dim lstrw As Long
lstrw = lastrow (DPCustomerDates)
'Copy DP-CustomerDates
DPCustomerDates.Range("A1:D" & lstrw).Copy
End Sub

Upvotes: 1

Related Questions