To reset an inflated used range in Excel VBA, delete the unused rows below and columns to the right of the sheet’s real data, then save and reopen the workbook. The macro below does that on the active worksheet; make a backup first, because deleting rows or columns cannot be undone with Ctrl+Z.
What Excel’s Used Range Means
A worksheet’s used range is the area Excel considers to have been used. It can extend beyond the visible data because cells may contain formulas, values, or formatting, including formatting applied accidentally to entire rows or columns. The range matters for navigation, printing, some workbook operations, and file size in certain cases. It does not mean every cell in that rectangle contains meaningful information.
Press Ctrl+End in Excel for Windows to jump to the worksheet’s last cell, as Excel currently recognizes it. If your actual data ends at, for example, H250 but Ctrl+End lands at XFD1048576, the used range may be larger than necessary. That jump is a useful clue, not a complete diagnosis: a cell with formatting or a formula that displays an empty string may still affect what Excel considers used.
Excel’s UsedRange property reports the range; it is not a command that clears extra cells. Simply reading ws.UsedRange in VBA may prompt Excel to refresh its internal view, but it does not reliably remove excess formatting or content. To reduce the range, remove what is beyond the genuine data and let Excel update the workbook.
VBA Macro to Remove Unused Rows and Columns
This macro finds the last cell containing a constant or formula, deletes complete rows below it and complete columns to its right, and then references the worksheet’s used range. It leaves the rectangular area from A1 through the last detected row and column intact, including blank spaces within that area.
Sub ResetUsedRangeOnActiveSheet()
Dim ws As Worksheet
Dim lastRowCell As Range
Dim lastColCell As Range
' Work only on the currently selected worksheet.
Set ws = ActiveSheet
' Find the last cell with a value or formula by row and by column.
Set lastRowCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
Set lastColCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
' Avoid deleting the whole sheet when it has no values or formulas.
If lastRowCell Is Nothing Or lastColCell Is Nothing Then
MsgBox "No values or formulas were found. No rows or columns were deleted."
Exit Sub
End If
' Remove rows below the last detected row.
If lastRowCell.Row < ws.Rows.Count Then
ws.Rows(CStr(lastRowCell.Row + 1) & ":" & CStr(ws.Rows.Count)).Delete
End If
' Remove columns to the right of the last detected column.
If lastColCell.Column < ws.Columns.Count Then
ws.Range(ws.Cells(1, lastColCell.Column + 1), _
ws.Cells(1, ws.Columns.Count)).EntireColumn.Delete
End If
' Reference the range after the deletions.
ws.UsedRange
MsgBox "Unused rows and columns were deleted. Save, close, and reopen the workbook."
End Sub
In the code block, the HTML entities < and & represent the VBA operators < and & in HTML display; when copying into the Visual Basic Editor, use the actual VBA characters: less-than signs in the two comparisons and ampersands for string concatenation. The editor code should read If lastRowCell.Row < ws.Rows.Count Then and concatenate the row-number strings with &.
How to Run the Macro
- Save a backup copy of the workbook. If you need to keep the macro in the file, use an Excel Macro-Enabled Workbook (
.xlsm) rather than saving it as.xlsx. - Open the Visual Basic Editor with Alt+F11 on Windows. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and check Developer.
- In the editor, choose Insert > Module, paste the VBA code into the module, and return to Excel.
- Select the worksheet you intend to clean. Use Developer > Macros, choose
ResetUsedRangeOnActiveSheet, and select Run. Confirm that the active sheet is the right one before running it. - Save the workbook, close it, and reopen it. Press Ctrl+End again to check whether Excel now jumps near the real last cell.
On Excel for Mac, VBA is available in the desktop app, though menus, the keyboard, and some VBA capabilities differ from Windows. Use the Developer tab’s Macros command if available, or open the Visual Basic Editor from the Tools menu. On a compact Mac keyboard, a key combination may require Fn; use the app’s menu command if a Windows shortcut does not work. Excel for the web cannot run VBA macros, so open the workbook in desktop Excel to perform this reset.
What the Macro Finds—and What It Does Not
The two calls to Find search for any cell content, looking in formulas and searching backward once by row and once by column. Looking in formulas includes formulas even when their displayed result is an empty string, as well as constants such as numbers and text. Explicit search settings matter because Excel’s Find dialog remembers some settings from earlier searches; setting them in the macro makes the search more predictable.
The macro does not use the last visible value in a column, and it does not treat a cell that only has formatting as a data cell. That distinction is useful when formatting is the cause of an oversized range: the rows and columns beyond the actual data can be deleted, taking their formatting with them. However, a formula far below the main table, a stray note, or a single accidental value will be treated as genuine content and will set the boundary. Inspect the reported last cell before deleting anything if you are unsure.
The search considers the whole worksheet, including hidden rows and columns. It does not identify every object that might matter to a layout, such as a drawing or shape positioned far from the data. It also does not remove unwanted formatting inside the remaining data rectangle. If the true data occupies A1:H250 but the rectangle has extra formatting in A1:H250, that formatting remains after this macro runs.
Manual Reset and Other Approaches
For a one-time cleanup, the same essential operation can be done without VBA. First identify the true last data row and column. Select the first genuinely unused row below the data, then select through the bottom of the worksheet and delete those rows. Repeat for the first unused column to the right and all remaining columns. Use the row and column headers to select entire rows or columns; pressing Delete on the keyboard usually clears cell contents but does not delete the rows, so it may leave formatting behind.
- On Windows, select a cell and use Ctrl+Shift+Down to extend a selection toward the bottom of a contiguous area, or use the Name Box to enter a row or column range. Check the selection before choosing Home > Delete > Delete Sheet Rows or Delete Sheet Columns.
- On Excel for Mac, shortcut behavior depends on the keyboard and selected range; use the row or column headers and the Home tab’s Delete options if the Windows shortcuts differ.
- For formatting alone, Home > Clear > Clear Formats removes formatting from selected cells, but it does not delete entire rows or columns. It can also remove formatting you wanted to keep, so select only the excess area.
Deleting whole rows and columns is generally the clearest way to remove content and formatting beyond the true boundary. Clearing contents alone is not equivalent. Recreating a worksheet can be an alternative for a badly damaged, simple sheet, but it requires care to preserve formulas, named ranges, validation, charts, print settings, and other worksheet features.
Why Save, Close, and Reopen?
After removing excess cells, Excel may not immediately show the expected last cell in every situation. Saving and reopening gives the application an opportunity to recalculate and store the worksheet’s effective dimensions. For that reason, a macro that only evaluates UsedRange is not a dependable substitute for deleting the unwanted rows and columns and reopening the file.
Check the result after reopening rather than judging only by file size. A workbook’s size depends on many things—images, pivot caches, embedded objects, formulas, styles, and other sheets—so it may not shrink noticeably even when the used range is corrected. Likewise, an improved Ctrl+End destination is evidence that the sheet boundary changed, but not proof that every workbook performance issue has been solved.
Common Problems and Safe Checks
- The macro stops on an apparently blank sheet. It deliberately skips sheets without values or formulas, which avoids deleting the entire worksheet. A sheet with only formatting is not detected by this search. Review such a sheet manually and clear or delete its excess formatting if appropriate.
- The last cell remains too far away. Look for formulas returning empty text, stray values, or notes in distant rows and columns. Hidden content counts. Remove only what you have confirmed is unnecessary, then save, close, and reopen.
- A table or layout changes after deletion. Deleting rows or columns can affect tables, references, merged cells, print layouts, and objects anchored to cells. Inspect the sheet and dependent formulas afterward. Avoid running the macro on a complex sheet until you have tested it on a copy.
- The workbook is protected. Excel may block deletion on a protected sheet. Unprotect it only if you are authorized and know the password or protection process, then restore the intended protection afterward.
- The file size does not change. Used-range cleanup is not a general-purpose compression tool. Other workbook content may account for most of the size.
Macros cannot be reversed with Ctrl+Z. The safest recovery plan is to close without saving if you spot a problem immediately, or restore the backup if changes have already been saved. Do not run a cleanup macro on a shared production workbook without coordinating with people who rely on its formulas or layout.
Frequently Asked Questions
Can I reset the used range with ws.UsedRange alone?
No. It returns a Range object and may prompt Excel to refresh its internal calculation, but it does not delete unwanted cells or formatting. Remove the excess rows and columns, then save and reopen the workbook.
Why does Ctrl+End go far beyond my visible data?
Excel may still consider distant cells used because they contain formatting, formulas, values, or other remnants. A formula that displays a blank result can still count. Find and inspect the distant cells before removing them.
Does this macro delete data inside my table?
It does not clear cells in the rectangular area from A1 through the last detected row and column. It does delete complete rows below that boundary and complete columns to its right, so confirm that no legitimate content or layout needs to remain there.
Will resetting the used range reduce my file size?
It can help when excess formatting or cell content is a significant contributor, but a smaller file is not guaranteed. Images, pivot data, formulas, embedded objects, and other workbook features can dominate file size.
Can I run this in Excel for the web?
No. Excel for the web does not execute VBA macros. Open the workbook in the desktop version of Excel for Windows or Mac, or delete the excess rows and columns manually in a desktop app.
Can I run the macro on every worksheet at once?
This version intentionally works only on the active sheet to reduce the risk of unintended changes. A workbook-wide version needs to loop through worksheets and should account for protected, empty, and specially structured sheets; test any such change on a backup first.
Related
- PKSolver in Excel: What It Does and How to Use It Safely
- Password Protect an Excel Sheet as Read-Only
- Password Protect an Excel Sheet From Viewing: What Works
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Insert Row in Excel: Shortcut and VBA OptionsIn Excel for Microsoft 365 on Windows, select the row where the new row should go with Shift+Space,…
- Macro Recorder for Windows: Options and How to ChooseA macro recorder for Windows captures repeated actions and replays them, but Windows does not provide one universal…
- Excel VBA Data Entry Form Template: Build and Use OneAn Excel VBA data entry form template is a reusable UserForm that collects values, checks them, and appends…
- VBA Global Dictionary: Scope, Setup, and Code ExamplesVBA has no built-in global dictionary type, but in Excel for Microsoft 365 on Windows you can create…
- VBA MsgBox Location: Where It Appears and How to Control ItA VBA MsgBox has no built-in argument for setting its screen coordinates. In Excel for Microsoft 365 on…
- VBA Dictionary: Search by Key and Check Whether It ExistsTo search a VBA Dictionary by key, call its Exists method before retrieving the value: use If dict.Exists(searchKey)…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.