Ravindra BagaleCourses & study guides मराठी Track your progress

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

About this data: Teaching sample for a fictional shop “Skyline Clinics”. Figures and names (e.g. Vikram Rao) are made up for this lesson on range cells and worksheets.

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 tasks (10+)

Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.

1. Rebuild the smallest example of range cells and worksheets on a blank sheet for Summit Schools.

Hint: Use only the steps on this page. Keep numbers numeric.

2. Change one input for Rohan Patil and confirm the result updates.

Hint: If the result is stuck, check absolute vs relative references.

3. Repeat the same steps with a £ or € value for Emma Clarke.

Hint: Formatting must not break SUM.

4. Write the result meaning in one plain sentence next to the cell.

Hint: Imagine explaining it to a new joiners.

5. Add a second row and fill the method down.

Hint: Double-click the fill handle if the pattern is consistent.

6. Break the example on purpose, then fix it using this lesson.

Hint: Common break: text instead of a number.

7. Apply range cells and worksheets to a 5-row made-up Summit Schools table.

Hint: Keep column headers clear.

8. Check your answer with a simple total or COUNT.

Hint: A wrong total usually means a format or key mismatch.

9. Swap ₹ and $ on one column and re-check totals.

Hint: Stored values stay the same; only the display changes.

10. List two mistakes beginners make with range cells and worksheets.

Hint: Look back at the tip box on this page.

11. Teach the steps out loud in under one minute.

Hint: Why → What → How.

12. Save the sheet as SummitSchoo_practice.xlsx.

Hint: Use a clear file name for class.

Loading practice tasks...

  1. Last used row (Easy): Which row number is the last filled row? The headings are in row 1.
  2. Late orders (Easy): How many rows have Status "Late"?
  3. Total amount (Easy): Add up the Amount column.
  4. Worst delay (Easy): What is the largest number in the Days late column?
  5. Late orders in Pune (Medium): How many orders are Late and in City "Pune"?
  6. Customer for an order (Medium): Find the Customer for Order ID "DL-704".
  7. Money stuck in late orders (Medium): Add up the Amount column for rows where Status is "Late".
  8. Big orders (Medium): How many rows have an Amount of 2000 or more?
  9. Message text (Medium): Build the text "N late orders", where N is the count of Late rows.
  10. Average delay of late orders (Hard): What is the average Days late for rows with Status "Late"? Round to 1 decimal.
  11. Tag for the first row (Hard): For the first row, return "Chase" if Days late is 3 or more, else "OK".
  12. Warning or all clear (Hard): Return "Warning" if any order is 3 or more days late, else "All clear".

See all practice tasks in the Task Hub →

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.

Revision card 1: What is Range Cells And Worksheets?
Card 1: What is Range Cells And Worksheets? · Download PNG
Revision card 2: Tiny example — Range Cells And Worksheets
Card 2: Tiny example — Range Cells And Worksheets · Download PNG
Revision card 3: Checklist — Range Cells And Worksheets
Card 3: Checklist — Range Cells And Worksheets · Download PNG