What is the significance of this excel vba code Sheet.Range.End().Row?

Question

What does this code below mean?

For i = 2 To Sheet3.[a65536].End(3).Row

I don't understand the significance of

Sheet.Range.End().Row

Answer 1

End Parameters

• The 3 means xlUp, but I have never seen anyone use it.

• The 'proper' way, using End to get the row of the last occupied cell in column A, in your case, would be:

  For i = 2 To Sheet3.Cells(Sheet3.Rows.Count, "A").End(xlUp).Row
  

  where xlUp makes it more readable while using Rows.Count makes it more flexible (since Excel 2007, the rows count is 1048576, 65536 was prior to it.) i.e. you can use the code in any version of Excel.

• In the following code the first two examples show how to get the last 'occupied' row where the first example is the preferred way, 'going' from the bottom cell up. The last two examples show how to get the last occupied column where the first example is the preferred way, 'going' from the right most cell to the left.

The Code

Sub explainEnd()
    
    Const FirstCol As String = "A"
    Const FirstRow As Long = 1
    
    Dim rg As Range: Set rg = Range("A1:J21")
    rg.Value = 1
    
    Dim RowOrCol As Long
    
    RowOrCol = Cells(Rows.Count, FirstCol).End(xlUp).Row ' 3 or -4162
    Debug.Print RowOrCol ' 21
    
    RowOrCol = Cells(FirstRow, FirstCol).End(xlDown).Row ' 4 or-4121
    Debug.Print RowOrCol ' 21
    
    RowOrCol = Cells(FirstRow, Columns.Count).End(xlToLeft).Column ' 1 or - 4159
    Debug.Print RowOrCol ' 10
    
    RowOrCol = Cells(FirstRow, FirstCol).End(xlToRight).Column ' 2 or -4161
    Debug.Print RowOrCol ' 10
    
End Sub