Answer

To delete the appearance of “infinite” unused columns and shrink the workbook’s used range, remove every column to the right of your actual data. Follow one of these reliable methods.

Quick delete by selecting to last column (Windows)

1. Click the header of the first empty column to the right of your data (for example, if your last data column is G, click H).

2. Press Ctrl + Shift + → (Right Arrow) to extend the selection to column XFD (the last column in modern Excel).

3. Right-click any selected column header and choose Delete.

4. Save the workbook, close Excel, then reopen the file to let Excel recompute the used range.

Quick delete by selecting to last column (Mac)

1. Click the header of the first empty column next to your data (for example, if your last data column is G, click H).

2. Press ⌘ (Command) + Shift + → (Right Arrow) to select through the last column.

3. Control-click (or right-click) any selected column header and choose Delete.

4. Save, close, and reopen the workbook to reset the used range.

Delete using the Name Box (alternate, works on Windows & Mac)

1. Determine the first empty column letter (for example, H).

2. Click the Name Box (left of the formula bar), type H:XFD and press Enter.

3. Right-click any selected column header and choose Delete.

4. Save and reopen the workbook.

Use VBA to remove every column after the last used column (Windows & Mac)

1. Press Alt + F11 (Windows) or Option + F11 (Mac) to open the VBA editor.

2. Insert a new module and paste this macro:

   Sub DeleteColumnsRightOfUsed()

       Dim ws As Worksheet

       Dim lastCol As Long

       For Each ws In ThisWorkbook.Worksheets

           On Error Resume Next

           lastCol = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

           On Error GoTo 0

           If lastCol < ws.Columns.Count Then

               ws.Range(ws.Columns(lastCol + 1), ws.Columns(ws.Columns.Count)).Delete

           End If

       Next ws

       ThisWorkbook.Save

   End Sub

3. Run the macro.

4. Close and reopen the workbook.

Reset used range without deleting columns (copy-to-new workbook)

1. Select the whole block of real data (exclude blank columns to the right).

2. Press Ctrl + C (Windows) or ⌘ + C (Mac).

3. Open a new workbook and paste the data.

4. Save the new workbook. This produces a file with a properly sized used range.

Why does Excel look like it has infinite columns

1. Modern Excel versions have a fixed column limit: 16,384 columns (A through XFD).

2. Excel stores a workbook’s used range; orphaned formatting, stray cells, or deleted data beyond your last real cell can make Excel treat far columns as “used.”

How to check and reset the used range

1. Press Ctrl + End to jump to the last cell Excel thinks is used.

2. If that cell lies far beyond your data, remove stray formatting or follow one of the delete methods above, then save/close/reopen to reset the used range.

Risks and what to watch for

1. Deleting columns may break formulas that reference whole columns (e.g., SUM(H:H)).

2. Named ranges, pivot tables, charts, or macros may refer to columns you delete.

3. Back up the workbook before bulk deletes.

Best practice to avoid “infinite columns.”

1. Keep data in clearly bounded ranges or convert ranges to an Excel Table (Insert → Table).

2. Avoid applying formatting to entire columns.

3. When cleaning a file, save a backup and check formulas and named ranges after the cleanup.

What if I cannot delete the columns, or the selection keeps reverting?

Try this checklist:

1. Check for worksheet protection and unprotect the sheet.

2. Inspect for merged cells spanning into the area you want to delete and unmerge them.

3. Remove conditional formatting or cell styles applied to entire columns.

4. Use the VBA macro above to forcibly delete columns after the last real column.

Are there differences between Windows and Mac?

Yes.

1. Keyboard shortcuts differ: Windows typically uses Ctrl while Mac uses ⌘ (Command).

2. The VBA code is identical across platforms, but opening the VBA editor uses Alt + F11 (Windows) and Option + F11 (Mac).

3. Right-click behavior can vary by mouse/trackpad settings on Mac.

Final quick recommendation

Use the select-first-empty-column → Ctrl/⌘+Shift+Right → Delete → Save → Close → Reopen flow for the fastest safe result. Use the VBA macro for multiple sheets or repeated cleanups. Back up the file before making bulk changes.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report