VBA & Macro Problems

How to Combine Task Assignees by Job Using VBA in Excel

Aamir Naveed AkramAamir Naveed Akram Nov 11, 2026 868 views

Question details

The user needs a method to consolidate the names of task assignees from multiple rows into their parent job row using line breaks, and then remove the redundant task rows.

How to Combine Task Assignees by Job Using VBA in Excel
Product
Excel
Device & OS
not provided
Scenario
Organizing task management data for reporting by grouping multiple assignees under their respective job titles.
Observed behavior
Task assignees are currently spread across multiple rows beneath a main job row, requiring a programmatic way to group and concatenate them into a single cell.
Before you start

Ensure you save a backup copy of your Excel workbook before running any VBA macros, as macro actions typically cannot be undone by pressing Ctrl+Z.

Solution 1Recommended

Use a Custom VBA Macro to Group and Combine Assignees

A custom VBA loop iterates through your data to identify job rows, appends assignees from subsequent task rows using line breaks, and deletes the processed task rows.

This method uses the Chr(10) character in VBA to insert line breaks between concatenated names, ensuring the final output is neatly formatted within a single cell. It requires writing a loop that processes the dataset from bottom to top to safely delete the secondary rows after merging.

1
Open the VBA Editor

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

2
Insert a New Module

Click 'Insert' > 'Module' from the top menu to create a blank script window.

3
Write the Concatenation Macro

Create a macro that loops through your dataset from bottom to top. Have it check if a row is a task row, append its assignee value to the job row above it using '& Chr(10) &', and then delete the task row using 'Rows(i).EntireRow.Delete'.

4
Run the Macro

Close the VBA editor, select your sheet, and press 'Alt + F8'. Select your newly created macro and click 'Run' to combine the assignees.

Enable Wrap Text: To see the line breaks properly in the consolidated cell, ensure 'Wrap Text' is enabled for the target column on the Home tab.
Efficient Data Processing with WPS Office

Manage Macros and Power Query in WPS Spreadsheet

WPS Spreadsheet fully supports advanced data processing, allowing you to easily consolidate rows and automate reporting tasks using VBA-compatible macros.

  1. 1. Open Your Data: Launch WPS Spreadsheet and open your task list workbook.
  2. 2. Access Developer Tools: Navigate to the 'Developer' tab on the ribbon to access the VBA Editor or macro settings.
  3. 3. Run the Macro: Paste your concatenation VBA code, run the macro, and instantly combine your task assignees.
Seamless compatibility with Microsoft Excel (.xlsx, .xlsm) formats.Built-in support for VBA macros and developer tools.Lightweight application with fast processing for large datasets.Free, user-friendly interface for effortless data management.
microsoft office alternative - wps office

Frequently Asked Questions

Why are the combined names showing on one line instead of separate lines?

If the names appear on a single line despite using Chr(10) in VBA or #(lf) in Power Query, you need to enable text wrapping for those cells. Select the cells, go to the Home tab, and click 'Wrap Text'.

Can I use a comma separator instead of a line break in VBA?

Yes, you can modify the VBA concatenation string. Replace 'Chr(10)' with '", "' in your VBA code to separate the assignees with a comma and a space.

Is Power Query better than VBA for concatenating rows?

Power Query is generally safer and more dynamic for grouping and concatenating large datasets because it does not permanently alter or delete your original source data like a VBA macro does. It is also easier to update when new data is added.

Does WPS Office support Excel VBA macros?

Yes, WPS Office provides built-in support for VBA macros in its Spreadsheet application, allowing you to seamlessly run row concatenation and data formatting scripts originally written for Microsoft Excel.