Fix VBA Macro That Reorders Names in the Wrong Excel Column
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.

- 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 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).
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.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications editor, then locate the module containing your name-reordering macro.
Ensure your code sets the active sheet properly by adding: Dim ws As Worksheet \n Set ws = ActiveSheet.
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.
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).

Use Built-in Excel Formulas Instead of VBA
If VBA is not strictly required, using native formulas is a safer and less error-prone way to achieve the exact same result.
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. Open Your File: Launch WPS Spreadsheet and open your macro-enabled workbook.
- 2. Access Developer Tools: Navigate to the 'Developer' tab on the top ribbon.
- 3. Edit the Macro: Click 'Visual Basic' to securely edit your column references and execute your script on the correct columns.

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.




