Power Query Problems

How to Extract Excel Rows When Any Date Column Falls Within a Range

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs to copy entire rows of data to another worksheet if any of the 14 date columns in those rows contain a date that falls between a specified start date in cell B1 and an end date in cell D1.

Product: Excel
Device & OS: not provided
Scenario: Filtering a large dataset and extracting records based on dynamic date range criteria across multiple columns.
Observed behavior: Matching rows need to be dynamically copied or returned to a summary worksheet for reporting purposes.

Before you start

Ensure all 14 columns containing dates are formatted consistently as 'Date' values rather than text. If you plan to use the VBA method, save a backup copy of your workbook first.

Solution 1Recommended

Extract Rows Using Power Query (Unpivot Method)

Power Query is the most robust and dynamic way to handle multiple columns without writing complex VBA code.

By unpivoting the non-essential columns, you consolidate all 14 date columns into a single column, making it much easier to apply a standard date filter.

Load data into Power Query

Select your data table, navigate to the 'Data' tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Unpivot the date columns

Select your non-date identifier columns (e.g., the first three columns). Right-click their headers and choose 'Unpivot Other Columns'. This transforms all 14 date columns into a single 'Value' column.

Filter the date range

Click the filter dropdown on the new 'Value' column, choose 'Date Filters' > 'Between...', and enter your start and end dates.

Load the extracted rows

Click the 'Home' tab, then select 'Close & Load' to output the filtered data into a new worksheet.

Dynamic Updates: If your original data changes, you can simply right-click the Power Query result table and click 'Refresh' to update the extracted rows automatically.

Solution 2

Extract Rows Using a VBA Macro

Use a VBA script to automate the process of scanning each row and copying it to a new sheet if a matching date is found.

Solution 3

Use Array Formulas to Return Rows to a Summary Sheet

Use native Excel formulas with Boolean logic to dynamically pull matching records into a summary sheet.

Advanced Data Extraction

Extract and Filter Data Easily in WPS Spreadsheet

WPS Spreadsheet offers powerful data processing tools, including advanced array formulas and VBA macro support, making it simple to extract rows based on complex date criteria without formatting limitations.

1. Open your dataset in WPS Office: Launch WPS Spreadsheet and open the file containing your multiple date columns.
2. Define your criteria: Set up your Start Date in cell B1 and your End Date in cell D1 on a summary sheet.
3. Apply the extraction method: Use the FILTER function or run your custom VBA macro directly within the WPS environment.
4. Save your work seamlessly: Save your extracted report in the standard .xlsx or .xlsm format for complete cross-platform compatibility.

Fully compatible with Microsoft Excel formats, including .xlsx and .xlsm files.Supports dynamic array functions like FILTER to instantly pull data based on start and end dates.Built-in support for VBA macros allows you to automate repetitive row-copying tasks.Free to download, lightweight, and features a highly intuitive user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why are my dates not filtering properly in Power Query?

This typically occurs when your date columns are formatted as Text instead of Date. In the Power Query Editor, click the data type icon (ABC) next to the column header and change it to 'Date' before applying the between filter.

Do I have to save my file as .xlsm if I use the formula method?

No. If you extract rows using Power Query or native array formulas like FILTER, you can safely save your file as a standard .xlsx workbook. The .xlsm format is strictly required only if you are using VBA macros.

Can I extract rows based on additional criteria besides date range?

Yes. In Power Query, you can simply add more filter steps to other columns. If using the FILTER function, you can add more criteria using the '*' operator for 'AND' logic or '+' for 'OR' logic within your formula.

Excel & Data Problems