To edit a cell or range with Excel VBA, assign a value or formula to a Range object, for example Worksheets("Sheet1").Range("A1").Value = "Complete". To change several cells, address the full range and assign values, formulas, formatting, or other properties; qualify the worksheet so the code changes the intended sheet.
What “edit a range” means in VBA
In Excel, a range is one cell or a group of cells. VBA represents it with the Range object, which exposes properties such as Value, Formula, and NumberFormat, along with methods such as ClearContents. Editing a range can therefore mean changing its contents, inserting a formula, changing its appearance, or clearing data. These actions are separate: changing a value does not automatically change the cell’s formatting, and clearing contents does not necessarily remove formatting.
The simplest assignment changes a cell’s value. The following macro writes a text value to A1 on Sheet1:
Sub SetOneCell()
' Write a value to a specifically named worksheet and cell.
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("A1").Value = "Complete"
End Sub
ThisWorkbook means the workbook containing the VBA code. That is often safer than ActiveWorkbook, which means whichever workbook happens to be active when the macro runs. If the sheet name or workbook reference is wrong, the macro may fail or edit a different workbook than intended.
Choose the right way to refer to cells
For a small, fixed task, an address such as Range("B2:D10") is straightforward. Use Cells(row, column) when row or column numbers are calculated in code, such as Cells(2, 3) for C2. Both forms can be qualified with a worksheet variable, as in ws.Cells(2, 3). Qualifying references matters because an unqualified Range or Cells usually refers to the active sheet, which can change if a user clicks another tab.
A range can also be built from two cell references. For example, ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, 1)) represents column A from row 2 through the row held in lastRow. This is useful when a list grows over time. A common way to find the last used row in column A is to start at the bottom of the worksheet and move upward with End(xlUp). The result depends on the chosen column: if that column has gaps or is not the best indicator of the data’s extent, the detected last row may not represent the whole table.
Named ranges are another option. A workbook-level name can make code easier to read, for example Range("InputCells"), but names can refer to multiple areas or have workbook-versus-sheet scope. Confirm the name in Formulas > Name Manager if code does not target the cells you expect.
Change values, formulas, and multiple cells
Use .Value when the goal is to put a constant value into a cell or range. Assigning a single value to a multi-cell range fills every cell in that range with that value. To give cells different values in one operation, assign a two-dimensional array whose dimensions match the target range. For example, this macro writes three different values to A1:A3:
Sub WriteSeveralValues()
' Assign a two-dimensional array to a three-cell range.
Dim ws As Worksheet
Dim entries(1 To 3, 1 To 1) As Variant
Set ws = ThisWorkbook.Worksheets("Sheet1")
entries(1, 1) = "North"
entries(2, 1) = "Central"
entries(3, 1) = "South"
ws.Range("A1:A3").Value = entries
End Sub
For formulas, use .Formula to assign a formula using traditional A1-style references. This example places a sum formula in D2:
Sub AddFormula()
' Enter a worksheet formula in D2.
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("D2").Formula = "=SUM(B2:C2)"
End Sub
Excel calculates the formula according to the workbook’s calculation settings. In code, formula text is a string, so it needs quotation marks around it. The formula’s function names and separators should be supplied in the form VBA expects for .Formula; do not add a leading apostrophe unless you deliberately want text rather than a working formula. When you need localized formula syntax or newer dynamic-array behavior, check the documentation for the specific property and Excel version rather than assuming every formula entry method behaves identically.
Edit a variable-size range without selecting it
Macros do not need to select cells before changing them. Directly referring to a worksheet and range is usually clearer and avoids relying on the current selection. The next example finds the last nonblank cell in column A and writes a status to the corresponding cell in column B. It leaves B1:B? alone if there are no data rows below the header.
Sub MarkRows()
' Find the last entry in column A and mark each data row in column B.
Dim ws As Worksheet
Dim lastRow As Long
Dim target As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
If lastRow >= 2 Then
Set target = ws.Range(ws.Cells(2, 2), ws.Cells(lastRow, 2))
target.Value = "Review"
End If
End Sub
This example assumes row 1 contains headers and column A has a value for each data row. If the data is an Excel Table, a table column or its DataBodyRange may be a more reliable target than guessing the last row. A table’s data body excludes its header row and, when present, its totals row. Check that the table contains data before using its data body, because an empty table can have no data-body range.
Change formatting or clear contents
Range properties can alter appearance without changing the underlying value. For example, ws.Range("A1:A10").NumberFormat = "0.00" displays numeric values with two decimal places; it does not round or replace the stored numbers. Formatting text as a date likewise does not convert arbitrary text into a valid Excel date. To set a fill color, code can use the range’s Interior property, while font appearance is controlled through Font.
Use the clearing method that matches the intent:
ClearContentsremoves values and formulas but retains formatting and other cell characteristics.ClearFormatsremoves formatting while leaving values and formulas in place.Clearremoves contents and formatting, so it is broader than either of the first two.
For example, ws.Range("B2:B20").ClearContents empties those cells without removing their number formats or fill colors. Be careful with whole-row, whole-column, or large-range operations: an unintended address can erase substantial data, and Ctrl+Z generally cannot reverse a macro’s changes.
Run and test a range-editing macro
On Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, and place a macro in that module. Run a procedure from the editor with the cursor inside it and F5, or return to Excel and use Alt+F8 to open the Macro dialog. On a Mac, the Visual Basic Editor shortcut is commonly Option+F11; some keyboards require Fn as well, and the available shortcut can depend on macOS keyboard settings. Excel for the web can open workbooks containing VBA, but it cannot create, edit, or run VBA macros; use the desktop app.
Before running code that changes cells, save a separate copy of the workbook. Macros cannot be undone with Ctrl+Z, so a copy is the dependable way to recover the original state. Test against a small range first, verify the worksheet and addresses, and inspect the result before applying the code to a large dataset. If Excel blocks a macro, review the workbook’s file type and security settings rather than enabling macros from an untrusted source. On Windows, macro options are under File > Options > Trust Center > Trust Center Settings > Macro Settings. Settings and security controls differ on Mac; use Excel’s security preferences and follow your organization’s policy.
Common errors and how to avoid them
A frequent cause of unexpected edits is an unqualified range. For instance, Range("A1").Value = 1 can act on the active worksheet, not necessarily the sheet a programmer intended. Assign a worksheet variable and use ws.Range("A1") consistently. Another common issue is a misspelled sheet name: Worksheets("Sheet1") must match the tab’s actual name exactly, including spaces.
Range dimensions matter when writing arrays. A two-dimensional array assigned to a range should have the same number of rows and columns as the target. A mismatch can produce an error or unexpected results. Also distinguish a cell’s displayed value from its stored value: number formats can make the same underlying number appear as a date, currency, or rounded figure. If a macro appears to write the “wrong” result, check the formula bar, the cell format, and whether the value was converted to text.
Protected sheets can prevent edits, and merged cells can make range assignments behave differently from a regular rectangular block. If a worksheet is protected, the code may need to work within the sheet’s protection design; do not remove protection or hard-code passwords without authorization. Events are another consideration: changing a cell can trigger a worksheet change event, which may run additional code and alter other cells. If a macro behaves differently from a direct edit, inspect the sheet’s event procedures and any workbook-level event code.
VBA versus editing cells by hand
Manual editing is often the best choice for a one-off correction because the user can see the target cells and use Excel’s undo history. VBA is more useful when the same edit must be repeated, applied consistently to many records, or performed as part of a larger process. For routine transformations, Power Query can be a better fit: it records repeatable data-cleaning steps and refreshes them without writing VBA. Worksheet formulas are preferable when results should update automatically as source cells change. VBA is most appropriate when the task needs a button, workbook event, or sequence of actions that formulas and standard data tools do not handle as simply.
VBA also has practical limits. It is desktop automation, not a mechanism for running a macro in Excel for the web, and organizations may restrict macros for security. A macro can overwrite user-entered values, so clear documentation, confirmation prompts for destructive actions, and a tested backup process are useful safeguards. For a small fixed range, direct assignments are easy to maintain; for a large or changing dataset, derive the range from a reliable table or data boundary rather than hard-coding a row count.
Frequently Asked Questions
How do I change a cell value using Excel VBA?
Qualify the worksheet and assign to its Value property, such as Worksheets("Sheet1").Range("A1").Value = "Done". For reusable code, store the worksheet in a variable and use that variable for every range reference.
How do I edit a whole range at once in VBA?
Address the full range, such as ws.Range("A1:A10").Value = "Checked", to put the same value in each cell. To write different values, assign a two-dimensional array with dimensions that match the target range.
What is the difference between Range and Cells in VBA?Range("B3") uses an A1-style address, while Cells(3, 2) uses row and column numbers. They can both refer to the same cell, and both should be qualified with the worksheet when the intended sheet must not depend on which tab is active.
Can I undo a VBA range edit with Ctrl+Z?
Usually not. Running a macro clears Excel’s normal undo history, so save a separate workbook copy before running code that changes data. For a destructive macro, test it on a copy or a small sample first.
Can VBA edit ranges in Excel for the web?
No. Excel for the web can preserve a workbook that contains VBA, but it does not run or edit VBA macros. Open the workbook in the Excel desktop application to use the Visual Basic Editor and run a macro.
Related
- Excel Text to Columns: How to Split Data
- Excel Text to Columns: How to Enter a Delimiter
- Excel Text to Columns: Can It Split Capital Letters?
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Hyperlink to Worksheet: A Practical GuideTo make a cell link to another worksheet with VBA, use Hyperlinks.Add and set Address to an empty…
- Find a Z-Score in Excel VBA: A Simple Step-by-Step GuideTo find z-scores with Excel VBA, calculate the mean and standard deviation of your data, then use the…
- Keyboard Recorder Macro: How It Works and When to Use OneA keyboard recorder macro is a tool that captures a sequence of keystrokes—and sometimes mouse actions or delays—so…
- Microsoft VBA Can’t Find Project or Library?“Can’t find project or library” is usually a VBA compile error caused by a missing, broken, or incompatible…
- Excel VBA Expected Expression Error: Causes and FixesIn Excel VBA, Expected: expression is a compile error: VBA reached a place where it needs a value…
- Excel VBA MsgBox Carriage Return: Line Breaks ExplainedTo put a carriage return and line break in an Excel VBA MsgBox, join the message parts with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.