VBA & Macro Problems

How to Move Excel Values Ending in (Cr) Using a VBA Macro

Kushani Nimanthika Oct 9, 2026 869 views

Question details

The user needs a script to automatically find cells ending with the suffix (Cr), move their contents to a different column, and clear the original cells.

How to Move Excel Values Ending in (Cr) Using a VBA Macro

Product: Excel
Device & OS: not provided
Scenario: Automating data cleaning and reorganization where specific text values must be shifted to a new column based on their suffix.
Observed behavior: The user requires a reliable VBA macro that successfully scans a source column, copies matching cells to a destination column (such as D to E), and leaves the original source cells empty without skipping rows.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your spreadsheet settings before running the script.

Solution 1Recommended

Use a Bottom-to-Top VBA Loop to Move Cells

Create and run a VBA macro that iterates through a column backwards to safely move specific values and clear the source cells without skipping rows.

When deleting or clearing data across rows in VBA, it is a best practice to loop from the bottom to the top. This prevents the macro from skipping rows if the data structure shifts during execution.

You can easily customize the script to match your specific source column, destination column, and worksheet name.

Open the VBA Editor

Press Alt + F11 on your keyboard to launch the VBA Editor.

Insert a New Module

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

Write the Macro Loop

Write a loop using 'For i = LastRow To 1 Step -1'. Inside the loop, use 'If Right(Cells(i, SourceCol).Value, 4) = "(Cr)" Then' to identify the target cells.

Move and Clear Contents

Within the If statement, set the destination cell value to equal the source cell value, then apply '.ClearContents' to the source cell.

Run the Macro

Close the VBA Editor, press Alt + F8, select your newly created macro from the list, and click 'Run'.

Use a Bottom-to-Top VBA Loop to Move Cells

Update Column References: Remember to update the source and destination column indexes (e.g., changing from column D to column E) inside your VBA code to match your actual workbook layout.

Advanced Data Handling in WPS Spreadsheet

Run VBA Macros Seamlessly in WPS Office

WPS Spreadsheet provides powerful data processing capabilities, including support for VBA macros in its advanced editions. You can easily automate repetitive tasks like moving specific text values without rewriting your existing Excel scripts.

1. Open Your Macro Workbook: Launch WPS Spreadsheet and open your .xlsm file containing the data.
2. Access the Developer Tools: Navigate to the 'Tools' tab and click on 'Macro', or simply press Alt + F8 to access the macro environment.
3. Insert Your Script: Paste or modify your customized VBA code for moving the '(Cr)' values to your desired columns.
4. Execute the Automation: Run the macro to instantly reorganize your columns and clear the source cells.

Fully compatible with Microsoft Excel (.xlsx and .xlsm) macro-enabled formats.Familiar VBA editor interface for seamless macro migration and editing.Lightweight software that processes large datasets quickly and efficiently.Built-in advanced formulas for alternative non-macro data manipulation.

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my VBA macro moving data from column D to column E?

Ensure that you have updated the column references in your VBA script to target Column D as the source and Column E as the destination. Also, verify that the active worksheet matches the sheet name specified in your code.

Why should I scan rows from bottom to top when clearing cells?

Looping from bottom to top (Step -1) prevents the macro from skipping rows. If you loop top-to-bottom and delete or clear a row, the remaining rows may shift, causing the row counter to miss the next consecutive entry.

How do I identify strings ending in '(Cr)' in VBA?

You can use the built-in VBA 'Right()' function. Using an expression like 'Right(CellValue, 4) = "(Cr)"' checks if the last four characters of the cell's text exactly match the suffix.

Excel & Data Problems