.CELLS.END: what is this doing?

[itr674]

In the below statement what is .Cells(65536,z).End(3)(2) saying?

.Cells(65536, z).End(3)(2) = Left$(ws.Name, Len(ws.Name) - 6)
.Hyperlinks.Add _
Anchor:=.Cells(65536, z).End(3), _
Address:="", _
SubAddress:="'" & ws.Name & "'!A1"

 

[Richie(UK)]

Hi em,

Have a look in the VBE Help files at the End property and the Item property. This will add some meat to the bare bones explanation below:

Sub Test()
    Dim z As Integer
    
    z = 1
    With ThisWorkbook.Worksheets("Sheet1")
        MsgBox .Cells(65536, z).End(3)(2).Address
        'start from row 65536 in the column referenced by z
        
        'Use the third argument of the End property (ie Up) :
        '   xlToLeft, xlToRight, xlUp, or xlDown
        'to find the last cell containing data
        
        'the (2) represents the 2nd Item from the cell found above
        'the first item is the cell itself so the 2nd represents the
        'next cell down (ie the next blank cell)
        
    End With

End Sub

HTH

 

[itr674]

Richie(UK) - thanks. I am studying the help files now...