VBA & Macro Problems

How to Select and Move Rows After Finding Text Using VBA in Excel

Anum Mushtaq Oct 23, 2026 869 views

Question details

The user needs an Excel VBA macro to search for specific text within Column B, select the data range from that row down to the end (covering Columns A through F), and move it to a different worksheet.

How to Select and Move Rows After Finding Text Using VBA

Product: Excel
Device & OS: not provided
Scenario: Automating the process of extracting and moving a specific range of data based on a defined text string within a targeted column.
Observed behavior: The user requires a VBA script that successfully identifies the first occurrence of the text, selects the appropriate multi-column range dynamically, and cuts/pastes the targeted rows to a destination worksheet.

Before you start

Before running the macro, ensure you have a destination worksheet created with the exact name referenced in the code (e.g., 'Change Requests') and save your workbook as a Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Use a VBA Macro to Find Text and Move the Range

This VBA script finds the first occurrence of a specific string in Column B and moves the selected data block (Columns A to F) to another worksheet.

This macro utilizes the VBA Find method to locate the specific text string. It then uses the Offset property to step back to Column A, captures the range up to Column F down to the last used row, and executes a cut-and-paste command to move it to the destination sheet.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor in your spreadsheet application.

Insert a New Module

In the VBA Editor, click on 'Insert' in the top menu bar, and select 'Module' to create a blank code window.

Paste the Macro Code

Copy and paste the following code into the module window: Sub TestMacro() Dim R As Range Set R = Range("B:B").Find("Change Request") If R Is Nothing Then Exit Sub Range(R.Offset(0, -1), Cells(Rows.Count, "F").End(xlUp)).Cut _ Worksheets("Change Requests").Cells(Rows.Count, "A").End(xlUp)(2) End Sub

Verify Worksheet Names

Ensure that the destination worksheet name in the code ("Change Requests") matches the actual tab name in your workbook exactly.

Run the Macro

Place your cursor inside the macro code and press F5, or click the 'Run' button (the green play arrow) in the toolbar to execute the script and move your data.

Customizing the Macro: You can change 'Change Request' to any text you need to find. To expand or reduce the number of columns selected, simply change the 'F' in Cells(Rows.Count, "F") to your desired end column letter.

Use Macros in WPS Spreadsheet

Automate Your Workflow with VBA in WPS Spreadsheet

WPS Office offers robust support for VBA macros, allowing you to run, edit, and create custom scripts to automate repetitive tasks like finding text and moving rows with ease.

1. Enable the Developer Tab: Open your document in WPS Spreadsheet, go to the options menu, and ensure the 'Developer' tab is enabled on your ribbon.
2. Access the VBA Editor: Navigate to the Developer tab and click on 'Visual Basic' or simply press Alt + F11 to launch the macro editor.
3. Run Your Script: Insert a new module, paste your custom macro code to find and move rows, and click Run to automate your task seamlessly.

Fully compatible with Microsoft Excel VBA macros and .xlsm formatsLightweight and fast execution for heavy datasetsBuilt-in Developer tab for easy macro management

microsoft office alternative - wps office

Frequently Asked Questions

How do I modify the VBA code to search a different column?

To search a different column, change the `Range("B:B")` portion in the script to the column you want to search. For example, use `Range("C:C")` to search within Column C.

Why isn't my VBA macro finding the text?

Ensure that the text in the code exactly matches the text in your cell, including spelling and spacing. The `Find` function can also be modified to match case or whole cell contents by adding arguments like `LookAt:=xlWhole`.

Can I copy the rows instead of cutting and moving them?

Yes. In the macro code, replace the `.Cut _` command with `.Copy _`. This will duplicate the data range and paste it to the new worksheet without removing the original data from the source sheet.

Excel & Data Problems