CODEWITHSUNDEEP
excelvba
Some One
Some One

Reputation: 55

How to refer to a sheet by name in function for last row?

I wrote a function to find the last row.

Sub test()
    Msgbox LROW(1,1)
End Sub
    
Function LROW(shtNumber as Integer, Col As Integer) as string
    Dim shtName as string
    ShtName="Sheet" & shtNumber
    LROW=ShtName . Cells(rows.count, col).End(xlUp).Row
End Function

I receive error

invalid qualifier

Upvotes: 0

Views: 54

Answers (1)

FunThomas
FunThomas

Reputation: 29181

ShtName is a String (that holds the name of a sheet), not a sheet.

Use something like

Dim shtName as string
ShtName="Sheet" & shtNumber
Dim ws as Worksheet
Set ws = Worksheets(ShtName)  ' Consider to use ThisWorkbook.Worksheets(ShtName)
LROW = ws.Cells(rows.count, col).End(xlUp).Row

Upvotes: 1

Related Questions