Range, Cells, Offset, .End(xlUp) — finding the last row

1. Range — by address

ws.Range("A1").Value = "Name"
ws.Range("A1:E1").Font.Bold = True
ws.Range("A:A").ColumnWidth = 20

Good when the address is fixed.

2. Cells — by row and column number

ws.Cells(2, 1).Value          ' row 2, column 1 = A2
ws.Cells(2, "B").Value        ' column letter works too

Perfect inside loops, because row and column can be variables:

ws.Cells(i, 3).Value = ws.Cells(i, 2).Value * 1.18

Combine both to build a range:

ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, 5))   ' A2 to E(lastRow)

3. Offset — move from a cell

ws.Range("A1").Offset(1, 2)   ' 1 row down, 2 columns right = C2

Negative numbers move up or left.

4. Resize — change the size

ws.Range("A2").Resize(10, 5)  ' 10 rows × 5 columns starting at A2 = A2:E11

5. Finding the last row — .End(xlUp)

Data grows every day, so never hard-code A2:A500. Use:

Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

How it works: start at the very bottom cell of column A (row 1,048,576), then do what Ctrl + ↑ does — jump up to the last filled cell. .Row gives its row number.

Pick a column that is always filled (like Date or Invoice No). A column with blanks at the end gives a smaller number.

Next empty row for adding a record:

nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

Last column in row 1:

lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

6. Why not .End(xlDown)?

Range("A1").End(xlDown) stops at the first blank cell. One empty row in the middle and your macro misses everything below it. If column A is completely empty below A1, it jumps to row 1,048,576. xlUp from the bottom avoids both problems.

7. CurrentRegion and UsedRange

ws.Range("A1").CurrentRegion   ' block around A1 until a fully empty row/column
ws.UsedRange                   ' everything Excel thinks was ever used

CurrentRegion is handy for a clean table. UsedRange can include old formatted cells, so don't rely on it for the last row.

8. Example: add a new entry at the bottom

Sub AddEntry()
    Dim ws As Worksheet, nextRow As Long
    Set ws = ThisWorkbook.Worksheets("Data")

    nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
    ws.Cells(nextRow, 1).Value = Date
    ws.Cells(nextRow, 2).Value = "Rahul Sharma"
    ws.Cells(nextRow, 3).Value = 25000
End Sub

Common mistakes

Hard-coding the last row. Using a column with gaps for .End(xlUp). Using Rows.Count without the sheet (ws.Rows.Count) when working across sheets. Mixing up Cells(row, column) order.