VBA & Macro Problems

Fix VBA Macro That Reorders Names in the Wrong Excel Column

Steve KSteve K Nov 10, 2026 870 views

Question details

The user needs to fix a VBA macro designed to reformat "First Name Last Name" into "Last Name, First Name" because the code is incorrectly reading data from Column A instead of the intended column.

How to Fix a VBA Macro Reordering Names in the Wrong Column
Product
Spreadsheets
Device & OS
not provided
Scenario
Running a VBA script to automatically process and reverse names within a specific worksheet column.
Observed behavior
The macro continually reads and processes data from column A, ignoring the user's selected column or the intended target column.
Before you start

Before modifying your VBA script, ensure you know the exact letters of your source column and destination column, and save a copy of your workbook as a macro-enabled file (.xlsm).

Solution 1Recommended

Update the VBA Code to Reference Specific Columns

Modify the macro to explicitly define the source and destination columns and calculate the last used row dynamically.

Hardcoded ranges in VBA are a common reason scripts run on the wrong column. By explicitly defining the worksheet and substituting column "A" with your actual target column, the macro will function correctly.

1
Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications editor, then locate the module containing your name-reordering macro.

2
Declare Explicit Worksheet Variables

Ensure your code sets the active sheet properly by adding: Dim ws As Worksheet \n Set ws = ActiveSheet.

3
Update the Target Column Letters

Locate where the code references the column. Change hardcoded "A" values to your desired source column (e.g., "C"). For example: lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row.

4
Set the Output Column

Modify the destination line to point to your new output column (e.g., "E"). The parsing logic should look like: ws.Cells(r, "E").Value = p(0) & ", " & Mid(Trim(ws.Cells(r, "C").Value), Len(p(0)) + 2).

Update the VBA Code to Reference Specific Columns
Dynamic Range Calculation: Using the expression ws.Cells(ws.Rows.Count, "C").End(xlUp).Row automatically finds the last row containing data, preventing the macro from running infinitely on empty cells.
Advanced Spreadsheet Operations

Easily Manage Macros and Data with WPS Spreadsheet

WPS Spreadsheet provides robust VBA support, allowing you to run, edit, and troubleshoot your existing Microsoft Excel macros effortlessly while offering powerful built-in text processing tools.

  1. 1. Open Your File: Launch WPS Spreadsheet and open your macro-enabled workbook.
  2. 2. Access Developer Tools: Navigate to the 'Developer' tab on the top ribbon.
  3. 3. Edit the Macro: Click 'Visual Basic' to securely edit your column references and execute your script on the correct columns.
Full compatibility with Microsoft Excel VBA macros (.xlsm) and scripts.Built-in Visual Basic Editor for seamless code debugging and updating.Smart Flash Fill feature that reformats names automatically without any coding required.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA macro always run on column A?

This typically occurs because the macro was recorded with absolute references or the column index in the VBA code is explicitly hardcoded as '1' or 'A'. Replacing '1' with your intended column letter (like 'C') resolves this issue.

How can I make my macro run on whichever column is currently selected?

Instead of using hardcoded column letters, replace your column references with 'Selection.Column'. This dynamically identifies the column index of the currently active cell you have clicked.

Is it better to use formulas or VBA for reordering names?

If you are only reordering names as a one-time task, using native formulas or the 'Flash Fill' tool is faster and avoids macro security risks. VBA is better suited if this action is part of a larger, highly repetitive automated workflow.