How to Change Multiple Non-Adjacent Row Heights with VBA in Excel
Question details
The user needs to automatically change the height of thousands of non-adjacent rows with specific heights (e.g., 1.5 or 3) to a new height (e.g., 5) without doing it manually.
- Product
- Microsoft Excel 2016
- Device & OS
- not provided
- Scenario
- Formatting an exported worksheet that contains thousands of rows with inconsistent heights.
- Observed behavior
- Thousands of non-adjacent rows have irregular heights of 3 or 1.5, requiring a programmatic VBA solution to resize them efficiently.
Before running any VBA macros, ensure you save a backup copy of your Excel workbook. Macros cannot be undone using the standard Undo feature, so testing on a copy prevents accidental data formatting loss.
Use a VBA Macro to Automatically Resize Rows
Applying a VBA script is the most efficient way to loop through thousands of rows and adjust the height of specific non-adjacent rows simultaneously.
This VBA macro loops through a specified range of rows from bottom to top. It checks if the row height is less than or equal to 3, and if so, changes it to 5. Turning off ScreenUpdating speeds up the execution significantly.
Press 'Alt + F11' on your keyboard to open the Microsoft Visual Basic for Applications window in Excel.
Click on 'Insert' in the top menu bar and select 'Module' to create a blank code window.
Copy and paste the following code into the module window: Sub MakeThemFive() Dim Ndx As Long Application.ScreenUpdating = False For Ndx = 2300 To 2 Step -1 If Rows(Ndx).RowHeight <= 3 Then Rows(Ndx).RowHeight = 5 End If Next Application.ScreenUpdating = True End Sub
Modify '2300 To 2' in the code to match the actual number of rows in your specific worksheet. For example, if your data goes down to row 5000, change it to '5000 To 2'.
Press 'F5' on your keyboard or click the 'Run' button (the green triangle icon) to execute the code and instantly resize the target rows.
Run VBA Macros Seamlessly in WPS Spreadsheet
WPS Office provides excellent compatibility with Microsoft Excel macros. You can easily run your VBA scripts to adjust row heights and automate tedious formatting tasks without modifying your code.
- 1. Open Your Workbook in WPS: Launch WPS Office and open your .xlsx or .xlsm spreadsheet.
- 2. Access the Developer Tools: Navigate to the 'Developer' tab on the ribbon. If it is not visible, you can enable it in the WPS settings.
- 3. Open the VBA Editor: Click on 'Visual Basic' or press Alt + F11 to open the VBA environment.
- 4. Insert and Run the Code: Insert a new module, paste your row-height resizing macro, and click 'Run' to format your spreadsheet instantly.
Frequently Asked Questions
Why do I need 'Application.ScreenUpdating = False' in my VBA code?
Turning off screen updating prevents the spreadsheet application from redrawing the screen every time a row's height is changed. This makes the macro run much faster, especially when processing thousands of rows.
How do I loop through all used rows instead of hardcoding the row number?
You can dynamically find the last used row by replacing '2300' with 'ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row' in your VBA loop. This ensures the macro adapts to worksheets of different lengths without manual code adjustments.
Can I use standard formatting tools instead of VBA for this?
While you can manually select non-adjacent rows by holding the Ctrl key and adjusting the height, doing this for thousands of rows is impractical. VBA is the only fully automated solution for conditionally resizing rows based on their current height.
How do I save a workbook that contains a VBA macro?
Workbooks containing VBA code must be saved as an Excel Macro-Enabled Workbook (.xlsm). If you save it as a standard .xlsx file, the application will prompt you that the macro code will be removed.
