VBA & Macro Problems

How to Move and Hide Rows Using an Excel VBA Macro

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs a VBA macro that automatically transfers data from columns A through U to a separate worksheet and hides the original row whenever the word 'Hired' is selected in column O.

Product: Excel
Device & OS: not provided
Scenario: Automating data management across worksheets based on drop-down list selections.
Observed behavior: A Worksheet_Change event is required to constantly monitor column O and automatically trigger the copy, append, and hide actions without manual intervention.

Before you start

Ensure your workbook contains a destination worksheet exactly named 'Hired' and that your file is saved as an Excel Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Use a Worksheet_Change Event Procedure

Implement a VBA macro tied to the specific worksheet that detects changes in column O, copies the targeted columns to the 'Hired' sheet, and hides the source row.

This solution relies on the Worksheet_Change event, meaning the code will run automatically every time a cell's value is modified. By temporarily disabling Application.ScreenUpdating and Application.EnableEvents, the macro prevents screen flickering and avoids infinite loops triggered by the macro's own changes.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in Excel.

Access the Specific Worksheet Module

In the Project Explorer pane on the left, double-click the name of the worksheet that contains your drop-down list to open its specific code module.

Insert the Event Macro

Copy and paste your Private Sub Worksheet_Change code into the module window. Ensure the code uses the Intersect method to restrict triggers only to column O (e.g., Me.Range("O2:O")).

Save as a Macro-Enabled Workbook

Close the VBA editor. Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the file type dropdown menu to ensure your code is preserved.

Important Error Handling Note: If the macro stops unexpectedly, Application.EnableEvents might remain set to False, preventing future macros from running. You may need to manually execute 'Application.EnableEvents = True' in the Immediate Window to restore functionality.

Advanced Spreadsheet Features

Run VBA Macros Seamlessly in WPS Office

WPS Office fully supports VBA macros, allowing you to easily automate tasks like moving and hiding rows. Its highly compatible Spreadsheet application ensures your XLSM files run flawlessly just as they do in Microsoft Excel.

1. Download and Install: Download WPS Office Free and complete the quick installation process.
2. Open Your Workbook: Launch WPS Spreadsheet and open your existing .xlsm file.
3. Enable Macros: Click 'Enable Macros' if a security warning prompts at the top of your screen.
4. Access VBA Editor: Navigate to the Developer tab and click 'Visual Basic' to edit or run your macros seamlessly.

Full compatibility with Microsoft Excel VBA syntax and objectsSeamlessly opens and saves .xlsm and .xlsb macro-enabled formatsLightweight application with fast macro execution speedsUser-friendly Developer tab for easy macro management

microsoft office alternative - wps office

Frequently Asked Questions

Why does the macro copy columns A through U specifically?

The specific range is determined by the offset and resize functions in the VBA code: `cel.Offset(0, -14).Resize(1, 21)`. Since the trigger cell is in column O (the 15th column), offsetting by -14 moves the starting point to column A, and resizing by 21 columns captures data up to column U.

Can I change the trigger word from 'Hired' to something else?

Yes. Locate the line in your VBA code that says `If cel.Value = "Hired" Then` and replace 'Hired' with your preferred keyword, making sure to keep the quotation marks.

Why did my macros stop triggering automatically after an error?

To prevent infinite loops, the code temporarily disables event triggers using `Application.EnableEvents = False`. If the code errors out before reaching `Application.EnableEvents = True`, Excel will stop listening for changes. You will need to manually reset it via the VBA Immediate Window.

Excel & Data Problems