VBA & Macro Problems

How to Combine File Paths for True Rows Using VBA in Excel

Elise Williams Oct 22, 2026 869 views

Question details

The user needs a VBA script to concatenate file paths from specific rows marked as 'True' and output the combined string.

How to Combine File Paths for True Rows Using VBA in Excel

Product: Excel
Device & OS: not provided
Scenario: Automating the extraction and combination of file paths based on a boolean condition in adjacent columns.
Observed behavior: The goal is to append matching paths from column C separated by a vertical bar (|) and display the final string in cell D1 without a trailing separator.

Before you start

Ensure you have saved a backup of your workbook and enabled the Developer tab in Excel to access the Visual Basic editor.

Solution 1Recommended

Create a VBA Macro to Concatenate File Paths

Write a custom VBA procedure that iterates through your data, checks the boolean condition, and concatenates the matching strings.

Using VBA allows you to dynamically loop through rows, evaluate criteria, and manipulate text strings much more efficiently than complex nested formulas.

Open the VBA Editor

Press Alt + F11 in Excel to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

Click 'Insert' in the top menu and select 'Module' to create a blank script window.

Write the Concatenation Logic

Create a sub procedure that finds the last used row. Loop through column B. If the cell value is True, append the corresponding value from column C and a vertical bar (|) to a string variable.

Remove the Trailing Separator

After the loop finishes, use the VBA Right, Left, and Len functions to strip the final vertical bar from the assembled string variable.

Output the Result

Assign the final string to Range("D1").Value and run the macro by pressing F5 or clicking the Run button.

Create a VBA Macro to Concatenate File Paths

Data Types Matter: Ensure that the 'True' values in column B are formatted as boolean logic values, not standard text. If they are stored as text, you must adjust your VBA code to check for the string "True" instead of the boolean True.

WPS Spreadsheet Automation

Easily Run VBA Macros in WPS Spreadsheet

WPS Office provides excellent compatibility with Excel macros, allowing you to run your VBA scripts to combine file paths and automate repetitive data processing tasks seamlessly.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook in the Spreadsheet application.
2. Access the Developer Tab: Navigate to the Developer tab on the top ribbon.
3. Open the VBA Editor: Click on 'VBA Editor' to launch the scripting environment.
4. Paste and Run: Insert a new module, paste your concatenation VBA code, and execute the macro directly.

Fully compatible with Microsoft Excel VBA and .xlsm files.Lightweight installation with a familiar spreadsheet interface.Advanced data processing and macro execution capabilities.

microsoft office alternative - wps office

Frequently Asked Questions

How do I handle empty cells in column C when combining paths?

Inside your VBA loop, add a secondary condition using 'If Not IsEmpty(Cells(i, 3).Value) Then' to check if the cell in column C contains data before concatenating it to your string variable.

Why is my macro giving a 'Type Mismatch' error?

This usually happens if you are trying to concatenate a cell that contains a formula error (like #N/A or #VALUE!). Ensure your VBA code checks for cell errors using the IsError function before processing the row.

How do I change the output cell from D1 to another location?

Locate the line of code that reads 'Range("D1").Value = yourVariable' and change "D1" to your desired cell reference, such as "E5" or even a different sheet like "Sheets("Sheet2").Range("A1")".

How do I save an Excel file that contains a VBA macro?

You must save the file in a macro-enabled format, such as an Excel Macro-Enabled Workbook (.xlsm), to ensure your VBA code is permanently saved with the document.

Excel & Data Problems