VBA & Macro Problems

How to Move Excel Notes Between Columns Using VBA

Nimra Malik Oct 9, 2026 869 views

Question details

The user needs to automate the process of appending content from a new notes column into a previous notes column across multiple rows without overwriting existing data.

How to Move Excel Notes Between Columns Using VBA

Product: Excel
Device & OS: not provided
Scenario: Managing large datasets where new status updates or notes need to be merged with historical notes in a different column, followed by clearing the new notes column for future entries.
Observed behavior: Without a macro, the user must manually copy, paste, and add line breaks for each row to combine notes. An automated script is required to process nonblank cells in the new column efficiently.

Before you start

Before running any VBA macro, ensure you have saved a backup copy of your workbook, as macro actions clear the undo history and cannot be reverted using the standard Undo shortcut.

Solution 1Recommended

Use a Custom VBA Macro to Append and Clear Notes

This method uses a VBA script to locate nonblank cells in your new notes column, append their values to the adjacent historical notes column using a line break, and then clear the original cells.

The provided macro targets column 'Z' for the new notes and uses an offset to shift the data to the column immediately to its left (column 'Y'). You will need to update the column references in the code if your worksheet layout differs.

Open the VBA Editor

Press Alt + F11 in your Excel workbook to open the Microsoft Visual Basic for Applications (VBA) window.

Insert a New Module

Click on 'Insert' in the top menu and select 'Module'. This will create a blank text window where you can write or paste your code.

Paste the Macro Code

Copy and paste the following code into the module window: Sub MoveKitNotes() Dim rng As Range Dim cel As Range Application.ScreenUpdating = False On Error Resume Next Set rng = Range("Z2:Z" & Rows.Count).SpecialCells(Type:=xlCellTypeConstants) On Error GoTo 0 If Not rng Is Nothing Then For Each cel In rng cel.Offset(0, -1).Value = Evaluate("TEXTJOIN(CHAR(10),TRUE," & cel.Address & "," & cel.Offset(0, -1).Address & ")") Next cel rng.ClearContents End If Application.ScreenUpdating = True End Sub

Run the Macro

Close the VBA Editor. In your worksheet, press Alt + F8 to open the Macro dialog box, select 'MoveKitNotes', and click 'Run' to execute the script.

Use a Custom VBA Macro to Append and Clear Notes

Adjusting Cell References: If your 'New Notes' are not in column Z, change 'Z2:Z' in the macro to your specific column letter. The offset '-1' dictates that the destination is one column to the left.

Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Spreadsheet features a built-in macro editor in its advanced versions, allowing you to write, edit, and run VBA scripts to automate repetitive tasks like merging column notes efficiently.

1. Open your Workbook: Launch WPS Spreadsheet and open the workbook containing your notes.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon.
3. Open the Macro Menu: Click on 'Macros' or press Alt + F8 to view your available scripts.
4. Execute the Script: Select the 'MoveKitNotes' macro from the list and click 'Run' to process the columns automatically.

Fully compatible with Microsoft Excel macro-enabled workbooks (.xlsm).Built-in Developer tab for easy access to the VBA editor.Lightweight software designed for fast processing of large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

How do I change the macro to target a different destination column?

You can change the destination by modifying the offset value in `cel.Offset(0, -1)`. The first number is the row offset, and the second is the column offset. Changing it to `cel.Offset(0, 1)` will move the notes one column to the right instead of the left.

How can I separate the combined notes with a comma instead of a new line?

In the macro code, locate the section `CHAR(10)` within the `Evaluate` function. Replace `CHAR(10)` with `", "` (including the quotation marks) to use a comma and a space as the delimiter.

Why isn't the macro doing anything when I click Run?

This usually happens if there are no nonblank constants in the specified column (e.g., column Z). The macro uses `SpecialCells(Type:=xlCellTypeConstants)` to find text. If your notes are generated by formulas rather than typed constants, the macro will not detect them.

How do I save a workbook that contains this macro?

You must save the file as an Excel Macro-Enabled Workbook (.xlsm). If you save it as a standard .xlsx file, the VBA code will be permanently deleted.

Excel & Data Problems