Use VBA to Resize WPS Spreadsheets Rows Before Exporting a PDF
Question details
The user needs a VBA macro in WPS Spreadsheets that temporarily autofits selected rows, exports a defined range to PDF, and then restores the original row height and wrap settings while keeping row 1 unchanged.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Exporting a worksheet range with wrapped or linked cell content to PDF without truncated rows.
- Observed behavior
- Rows with wrapped or linked data can have row heights that are too small for PDF output, causing clipped content.
Open the workbook in WPS Spreadsheets and enable the Developer tab so you can access the VBA editor. Verify that macros can run and note the current row height and wrap settings for the range you plan to export.
Run a VBA Macro to Autofit Selected Rows, Export PDF, and Restore Formatting
Use this repeatable method when you want a macro to autofit only the target rows, export the range as a PDF, and restore the original row height and wrap settings afterward.
The macro temporarily enables WrapText, autofits the selected rows, applies page setup options, exports the worksheet as a PDF, and then sets the row height back to 25 and disables wrapping.
In WPS Spreadsheets, press Alt+F11 or click the Developer tab and select Visual Basic.
Right-click the workbook in the Project Explorer, choose Insert > Module, and paste the PrintReport macro into the module window.
Change the range declaration from Set PrintRng = .Range("W1:AA93") to Set PrintRng = .Range("W12:AA63") so row 1 remains untouched and only rows 12 through 63 are processed.
Close the VBA editor, press Alt+F8, select PrintReport, and click Run. WPS Spreadsheets autofits the rows, exports the PDF, and then sets PrintRng.RowHeight = 25 and PrintRng.WrapText = False.
Manually Set the Print Area and Autofit Before Exporting to PDF
Use this one-time manual method if you prefer not to run a macro.
Export Excel-Compatible Ranges to PDF with WPS Spreadsheets VBA
WPS Spreadsheets supports VBA macros and Excel-compatible files, so you can run the same autofit-and-export routine to create PDFs without cut-off rows. The macro adjusts the active range, exports the sheet, and restores your original formatting automatically.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the .xlsx or .xlsm workbook that contains the range you want to export.
- 2. Open the VBA editor: Press Alt+F11 or go to the Developer tab and click Visual Basic.
- 3. Paste and adjust the macro: Insert a new module, paste the PrintReport macro, and set the range to W12:AA63 so row 1 is not changed.
- 4. Run the macro: Press F5 in the VBA editor or run the macro from the Macros dialog. WPS Spreadsheets autofits the rows, exports the PDF, and restores the original formatting.
Frequently Asked Questions
Why are my rows cut off when I export an Excel range to PDF?
When cells use Wrap Text or contain linked data, the stored row height can be smaller than the displayed content. Autofit the rows before exporting or use a VBA macro to do it automatically.
How do I keep row 1 unchanged in the VBA macro?
Change the print range declaration to Set PrintRng = .Range("W12:AA63") instead of W1:AA93. This excludes row 1 from the wrap, autofit, and row-height reset actions.
How can I restore the row height after the PDF is created?
Add PrintRng.RowHeight = 25 and PrintRng.WrapText = False after the ExportAsFixedFormat line. The macro then resets only the selected rows after exporting.
Does WPS Spreadsheets support ExportAsFixedFormat for PDF?
Yes. WPS Spreadsheets supports VBA and ExportAsFixedFormat with xlTypePDF, so you can run the same PDF export routine used in Excel.
What should I check if the macro does not run?
Make sure the Developer tab is visible, macros are enabled, and the worksheet is not protected. If the sheet is protected, include ActiveSheet.Unprotect at the start of the macro.
