VBA & Macro Problems

How to Use VBA to Concatenate Excel Rows and Transpose to One Column

Maira Mehtab Oct 21, 2026 868 views

Question details

The user needs to write a VBA macro that loops through rows of data, concatenates the values with a specific prefix (e.g., 1063), and writes the transposed output into a single column.

Using VBA to Concatenate Excel Rows and Transpose Results into One Column

Product: Excel
Device & OS: not provided
Scenario: Automating repetitive data entry tasks where row data needs to be merged, prefixed with a specific text string, and reorganized vertically into one column.
Observed behavior: The user is looking for the correct VBA procedure and logic to handle the source range, apply the required separators, and output the exact expected format into a single column.

Before you start

Before running any VBA scripts, identify your exact source data range and the required text separators, and always save a backup copy of your workbook.

Solution 1Recommended

Create a VBA Macro to Loop, Concatenate, and Transpose

Use a VBA For loop to iterate through each row of your dataset, combine the cell values with your desired prefix, and output the result into a continuous vertical column.

This solution involves writing a custom script in the VBA editor. The exact code structure depends on your data layout, but the general logic requires declaring a source range, looping through its rows, joining the cell values with the prefix '1063', and sequentially writing the joined string to an output column like Column A.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Microsoft Visual Basic for Applications (VBA) window.

Insert a New Module

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

Write the Concatenation Loop

Define your Sub procedure. Create a For Each loop that iterates through your specific data rows. Inside the loop, declare a string variable that concatenates '1063' with the row values, separating them with a comma or space as needed.

Define the Output Destination

Use a row counter variable to write each concatenated string into your target column (e.g., Cells(outputRow, 1).Value = concatenatedString), incrementing the row counter by 1 in each loop iteration.

Run the Macro

Press F5 or click the 'Run' button in the toolbar to execute the script and populate your output column.

Create a VBA Macro to Loop, Concatenate, and Transpose

Tip for Dynamic Ranges: Use dynamic range references like 'UsedRange' or 'End(xlUp)' in your VBA code so the macro automatically adjusts if you add or remove rows in the future.

Advanced Data Automation

Automate Data Concatenation in WPS Spreadsheet

WPS Spreadsheet provides excellent support for advanced data manipulation, including VBA macros (in supported versions) and powerful built-in text functions to seamlessly concatenate and reorganize your data.

1. Open WPS Spreadsheet: Launch WPS Office and open the workbook containing your target dataset.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click on 'Macros' or the 'VBA Editor'.
3. Insert Your Script: Create a new module, paste your concatenation and transposition VBA code, and modify the range references if necessary.
4. Execute the Automation: Run the macro to instantly process your rows, apply the prefixes, and transpose the output into a single column.

Full compatibility with Microsoft Excel formats (.xlsm, .xlsx, .csv)Supports advanced macro execution to automate repetitive tasksFeatures modern dynamic array functions for no-code manipulationLightweight, fast, and highly efficient for large datasets

microsoft office alternative - wps office

Frequently Asked Questions

Can I concatenate and transpose data without using VBA?

Yes. In modern spreadsheet applications, you can use built-in functions like TEXTJOIN to concatenate row values and TOCOL or TRANSPOSE functions to arrange the final results vertically without writing any code.

Why is my VBA macro returning a debugging error?

This usually happens if your declared source range does not match your actual data, or if there are empty cells causing an unexpected null value. Ensure your ranges are correct and consider adding an 'If Not IsEmpty' check in your loop.

How do I change the prefix from 1063 to something else?

Open your VBA editor, locate the string assignment line in your code (e.g., ResultString = "1063_" & CellValue), and simply replace '1063' with your new desired text.

How do I output the transposed data to a different worksheet?

Modify the destination object in your VBA code. Instead of referencing the active sheet, specify the target sheet by using code like Worksheets("Sheet2").Cells(outputRow, 1).Value = finalString.

Excel & Data Problems