How to Fix Excel VBA Row Height Resetting After Data Refresh
Question details
The user's custom VBA-assigned row heights reset or are lost when the Excel workbook completes a data refresh or recalculates formatting.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Running a VBA macro to format row heights in a worksheet that also contains refreshable data connections, Power Query tables, or standard external data.
- Observed behavior
- The custom row height settings applied via the VBA macro are immediately overwritten, resetting to default heights once the data refresh finishes.
Before making changes to your VBA code or connection properties, ensure you have saved a backup copy of your macro-enabled workbook (.xlsm) and identified which data connection or table is triggering the refresh.
Reorder VBA Code and Disable Background Refresh
The most common cause of this issue is an asynchronous data refresh finishing after the VBA row height command has already run. Triggering the height adjustment after the refresh completes resolves this.
When 'Enable background refresh' is active, VBA triggers the refresh but immediately moves to the next line of code. The data refresh then finishes later, overwriting any formatting (like row heights) you applied in the meantime.
In Excel, navigate to the Data tab, click 'Queries & Connections', right-click your connection, and select 'Properties'. Uncheck the 'Enable background refresh' option.
Open the VBA Editor (ALT + F11) and find the subroutine containing your RefreshAll or table refresh commands.
Place your row height formatting code (e.g., `Rows("1:10").RowHeight = 25`) explicitly after the line that refreshes the data. Since background refresh is off, the macro will wait to format the rows.
Disable Table AutoFit and External Data Formatting Settings
Excel tables sometimes automatically adjust row heights and column widths to fit new data upon refresh. Disabling this prevents your custom VBA heights from being overridden.
Consult Advanced VBA Developer Forums
If multiple macros are interacting or your data connections involve complex asynchronous queries, you may need advanced developer troubleshooting to isolate the conflict.
Try WPS Office for Smooth Macro & VBA Handling
If you are tired of complex formatting resets and cumbersome workbook configurations, consider switching to WPS Office. It provides a lightweight, highly compatible alternative with excellent support for VBA macros and Excel (.xlsm) formats.
- 1. Download WPS Office: Visit the official WPS website to download the free office suite installer for your operating system.
- 2. Open your workbook: Launch WPS Spreadsheets and open your .xlsm file containing the macros and data connections.
- 3. Run macros smoothly: Use the built-in Developer tools to run your VBA scripts seamlessly and apply your formatting without unexpected resets.
Frequently Asked Questions
Why does my VBA row height code work only sometimes?
This usually happens when asynchronous processes like Background Refresh are enabled. Occasionally, the macro finishes running before the data finishes loading, causing the final data refresh to overwrite your VBA formatting.
How can I disable Background Refresh directly in Excel VBA?
You can set BackgroundQuery to False in your VBA code (e.g., `ActiveWorkbook.Connections("ConnectionName").OLEDBConnection.BackgroundQuery = False`) prior to calling the refresh command to ensure the refresh completes before the next line of code executes.
Does WPS Office support VBA macros?
Yes, WPS Office offers robust VBA support, allowing you to run, edit, and debug macros created in Microsoft Excel with high compatibility.
