Ravindra BagaleCourses & study guides

12. Macros and VBA

12.7 Range, Cells and Worksheets

By Last updated:

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

Using .Select and Selection on every line, like the recorder does, makes a macro slow and makes it run on the wrong sheet – many students' macros write on the "Report" sheet instead of "Orders". Always write with the sheet, like ws.Range(...), and avoid Select. Remember the difference between ThisWorkbook and ActiveWorkbook.

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.