12.7 Range, Cells and Worksheets

By Ravindra Bagale Last updated: 25 September 2026

Code	Meaning
`Range("A1").Value = 100`	Write a value
`Range("A2:A10").ClearContents`	Clear values, keep formats
`Cells(2, 3).Value`	Row 2, column 3 (= C2) – great in loops
`Range("A1").CurrentRegion`	The block of data around A1 (like Ctrl + A)
`Range("A1").Offset(1, 2)`	One row down, two columns right (C2)
`Range("A1").Resize(5, 3)`	A1:C5
`Range("G2").Formula = "=F2*1.05"`	Write a formula
`Worksheets("Orders").Activate`	Go to a sheet
`Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Nashik"`	Add a sheet at the end
`ThisWorkbook` vs `ActiveWorkbook`	The file with the code vs the file currently in front

Worked example – without Select. The recorder writes Range("A1").Select then Selection…; good code works directly on objects:

Sub AddGSTColumn()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Orders")
    ws.Range("J1").Value = "Amount incl GST"
    ws.Range("J2:J11").Formula = "=G2*1.05"
    ws.Range("J2:J11").NumberFormat = "#,##0.00"
End Sub

Writing a formula to a whole range at once adjusts relative references row by row (J3 gets =G3*1.05), just like copying down.

Ravindra Bagale's Tip

Recorder sarkha pratyek line la .Select aani Selection vaparla ki macro slow hoto aani chukichya sheet var chalto – khup students cha macro "Orders" aivaji "Report" sheet var lihito. Nehmi ws.Range(...) asa sheet sobat liha, Select talava. ThisWorkbook aani ActiveWorkbook cha farak lakshat theva.

Practice task

Without using Select, write a macro that puts headers in Report!A1:C1 (City, Orders, Sales), fills the six city names below with Cells(r, 1), and writes COUNTIFS/SUMIFS formulas next to them.