VBA & Macro Problems

How to Use Excel VBA to Select Multiple Conditional Ranges for PDF Export

Tauseeq Magsi Oct 16, 2026 869 views

Question details

The user needs to use VBA to selectively compile multiple noncontiguous ranges containing text (including text generated by formulas) and export only those specific ranges to a PDF document.

How to Use Excel VBA to Select Multiple Conditional Ranges for PDF Export

Product: Excel
Device & OS: not provided
Scenario: Automating a reporting process where only worksheet sections containing valid data or text should be included in a final PDF export, while blank or non-relevant sections are ignored.
Observed behavior: The goal is to dynamically build a Union of target ranges based on conditional cell checks and successfully print or export that combined range to PDF without including non-qualifying pages.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your Trust Center settings before running or testing the VBA code.

Solution 1Recommended

Build a Union Range and Export to PDF

Loop through a list of candidate ranges, evaluate specific cells for text, combine qualifying areas into a single Union range, and execute the PDF export.

By dynamically evaluating specific cells within your defined areas using WorksheetFunction.IsText, you can construct a consolidated range object. This prevents the inclusion of blank pages or sections that do not meet your criteria in the final exported file.

Define Candidate Ranges

Open the VBA editor (Alt + F11). In your module, declare your range variables (e.g., Dim rngList As Range, cell As Range, exportRng As Range) and define the separate candidate areas you wish to evaluate.

Loop and Test Conditions

Write a For Each loop to iterate through each defined area. Inside the loop, use an If WorksheetFunction.IsText(cell.Value) statement to check if the target cell in that area contains text or formula-generated strings.

Build the Union Range

If the condition evaluates to true, add that area to your exportRng variable. Use a conditional check: If exportRng Is Nothing Then Set exportRng = area Else Set exportRng = Union(exportRng, area).

Export to PDF

Once the loop completes, verify that exportRng is not Nothing. Then, call exportRng.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\yourpath\file.pdf" to generate the document.

Formula Compatibility: The WorksheetFunction.IsText method successfully evaluates text even if the string is generated dynamically by an Excel formula rather than being hardcoded.

WPS Office Macro Support

Automate PDF Exports with VBA in WPS Spreadsheet

WPS Spreadsheet provides powerful support for VBA and macros, allowing you to run your Excel VBA scripts seamlessly. You can automate complex logic, select conditional ranges, and utilize built-in PDF tools with ease.

1. Open WPS Spreadsheet: Launch WPS Office and open your macro-enabled workbook (.xlsm) containing your reporting data.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click 'Visual Basic' to open the familiar VBA editor.
3. Execute the Export Script: Paste your Union range PDF export code into a module and press F5 to execute the automated conditional PDF export directly.

Fully compatible with Microsoft Excel VBA syntax and macro functionality.Built-in, high-quality PDF converter without the need for external print drivers.Lightweight software architecture for faster script execution and file processing.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA code exporting blank pages to the PDF?

This usually occurs if the entire active worksheet is being targeted for the export instead of the specific Union range. Ensure the `ExportAsFixedFormat` method is applied directly to your compiled `exportRng` object rather than `ActiveSheet`.

Can I use VBA conditions other than IsText to select ranges?

Yes. You can evaluate numeric thresholds (e.g., `cell.Value > 0`), check for specific string values, or use `Not IsEmpty(cell)` to customize exactly which areas get added to your final Union range for printing.

How do I prevent overlapping areas in my Union range?

When defining your initial candidate list, ensure the ranges are strictly non-contiguous. However, if overlaps are unavoidable, VBA's `Union` method automatically handles overlapping cells by combining them into a single reference without duplicating the cells in the output.

Does WPS Office support the ExportAsFixedFormat VBA method?

Yes, WPS Office versions that include VBA support fully recognize the `ExportAsFixedFormat` method, allowing you to export distinct ranges, charts, or entire worksheets directly to PDF using your standard Excel VBA code.

Excel & Data Problems