VBA & Macro Problems

How to Use VBA to Display Only Used Excel Cells on Screen

Adam Davis Oct 12, 2026 869 views

Question details

The user wants to adjust the Excel interface to display only the used cells in a worksheet by automatically fitting them to the screen.

How to Use VBA to Display Only Used Excel Cells on Screen

Product: Excel
Device & OS: not provided
Scenario: The user is working with a specific range of data and wants to focus entirely on those active cells by adjusting the window zoom to fit.
Observed behavior: The screen needs to dynamically adjust its zoom level to fit the active data range without permanently deleting or hiding unused rows or columns.

Before you start

Ensure you have saved a backup of your workbook and enabled the Developer tab in your ribbon to access the Visual Basic Editor.

Solution 1Recommended

Use a VBA Macro to Auto-Fit the Used Range

Run a short VBA script that selects the used cells and sets the active window zoom to fit the selection perfectly to your screen.

This method is quick and straightforward. The VBA code targets the UsedRange property of the ActiveSheet and applies the Zoom to Fit command, ensuring that all cells containing data are visible without requiring manual zoom adjustments.

Open the VBA Editor

Press the Alt + F11 keys on your keyboard to launch the Visual Basic for Applications (VBA) editor.

Insert a New Module

In the VBA editor, click on 'Insert' in the top menu bar and select 'Module' to create a blank script window.

Paste the Macro Code

Copy and paste the following code into the module window: Sub ShowUsedCells() ActiveSheet.UsedRange.Select ActiveWindow.Zoom = True End Sub

Run the Macro

Close the VBA editor and return to your worksheet. Press Alt + F8, select 'ShowUsedCells' from the list of macros, and click 'Run'.

Use a VBA Macro to Auto-Fit the Used Range

Temporary View Adjustment: This macro adjusts the selection and zoom level to fit your screen. It does not permanently delete or hide the unused rows and columns.

Solution 2

Manually Hide Unused Columns and Rows

If you want a more permanent restriction to prevent users from scrolling outside the used range, manually hiding the remaining blank cells is a practical alternative.

Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Office Spreadsheet provides excellent support for VBA macros, allowing you to run scripts to adjust worksheet views, automate tasks, and handle complex data sets easily.

1. Enable Developer Tools: Open WPS Spreadsheet, navigate to your options menu, and ensure the Developer tab is enabled on your main ribbon.
2. Access the VBA Editor: Click the 'Developer' tab on the top ribbon and select the 'VBA Editor' icon to open the scripting window.
3. Insert and Run Code: Insert a new module, paste your ActiveWindow zoom macro, and click 'Run' to fit your used cells to the screen instantly.

Outstanding compatibility with Microsoft Excel VBA macros and .xlsm filesBuilt-in Developer tools to write, edit, and execute scriptsLightweight software that loads large datasets quicklyFree alternative with a familiar and intuitive user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does the macro make the text look extremely large?

If your used range consists of only a few cells (for example, A1 to B3), the ActiveWindow.Zoom = True command forces Excel to zoom in excessively to fill the entire screen space. You can manually zoom out using the slider in the bottom right corner of the application.

How do I restrict scrolling to only the used cells instead of just zooming in?

To prevent scrolling outside a specific area without zooming, you can set the ScrollArea property in VBA. Open the VBA Editor, open the properties window for your specific sheet, and type your exact range (like A1:M15) into the ScrollArea field.

Does this macro automatically work on all sheets at once?

No, the ActiveSheet.UsedRange.Select command only applies to the specific worksheet you are currently viewing and working on. You would need to navigate to another sheet and run the macro again to apply the zoom adjustment there.

Excel & Data Problems