VBA & Macro Problems

How to Use a VBA Macro to Concatenate Selected Excel Cells

Nimra Malik Oct 12, 2026 869 views

Question details

The user needs a way to combine text from multiple selected cells into a single destination cell using a VBA macro, avoiding manual and repetitive string formulas.

How to Use a VBA Macro to Concatenate Selected Excel Cells

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Automating the concatenation of multiple highlighted cell values into a specific destination cell using a VBA script.
Observed behavior: A combined text string output is generated in a designated cell (such as C40) by looping through user-selected cells and joining their values with spaces.

Before you start

Ensure that the Developer tab is enabled in your spreadsheet application so you can access the VBA Editor, and verify that macros are allowed to run in your security settings.

Solution 1Recommended

Create and Run a Custom Concatenation Macro

Use a VBA script to loop through highlighted cells, join their text with spaces, and output the result to a specified destination.

This method involves writing a straightforward VBA loop. The code will systematically read the value of each cell in your active selection, append it to a running string separated by a space, and ultimately place the finalized text into a pre-defined destination cell.

Open the VBA Editor

Press the 'Alt + F11' shortcut on your keyboard, or navigate to the Developer tab on the ribbon and click on 'Visual Basic' to launch the VBA Editor.

Insert a New Module

In the Project Explorer pane on the left, right-click on your workbook's name, select 'Insert', and then click on 'Module' to create a blank scripting window.

Write the Concatenation Code

Paste a VBA script that loops through the selection (e.g., 'For Each cell In Selection'), builds the string (e.g., 'ResultString = ResultString & cell.Value & " "'), and outputs it to a cell (e.g., 'Range("C40").Value = Trim(ResultString)').

Assign the Macro to a Button

Return to your worksheet, insert a shape or Form Control button from the Developer tab, right-click the newly created button, select 'Assign Macro', and choose your concatenation macro for one-click execution.

Create and Run a Custom Concatenation Macro

Efficiency Boost: By assigning this macro to a worksheet button, you can highlight any dynamic group of cells and instantly combine their text into the target cell without repeatedly interacting with the VBA editor.

Automate Tasks with WPS

Run Macros Seamlessly in WPS Spreadsheet

WPS Spreadsheet offers excellent support for VBA macros, allowing you to easily create, edit, and execute scripts like cell concatenation. It provides a familiar, robust environment for automating your daily tasks.

1. Download and Install: Download WPS Office from the official website and complete the straightforward installation process.
2. Open Your Workbook: Launch WPS Spreadsheet and open your existing .xlsm or .xlsx file.
3. Access the Developer Tools: Navigate to the Developer tab to access the built-in VBA editor.
4. Run Your Macros: Write, paste, or execute your existing concatenation macros exactly as you would in Microsoft Excel.

Full support for VBA (Visual Basic for Applications) to write and execute custom concatenation scripts.Highly compatible with Microsoft Excel macro-enabled formats (.xlsm, .xlsx, .xls).Free, lightweight, and easy to use with a familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

How do I change the macro to separate concatenated text with a comma instead of a space?

You can modify the joining character directly in your VBA code. Locate the line that builds the string and change it from `ResultString = ResultString & cell.Value & " "` to `ResultString = ResultString & cell.Value & ", "`.

Why does my macro generate a security warning and fail to run?

By default, macros may be disabled to protect your system from potentially malicious scripts. Go to the Developer tab, click on 'Macro Security', and adjust your settings to enable macros for trusted workbooks.

Can I output the concatenated result to the last selected cell instead of a fixed cell like C40?

Yes. Instead of using a fixed range like `Range("C40").Value = ResultString`, you can dynamically output the result to the last cell in your selection using `Selection.Areas(Selection.Areas.Count).Cells(Selection.Areas(Selection.Areas.Count).Cells.Count).Value = ResultString` or simply prompt the user to select a destination.

Excel & Data Problems