VBA & Macro Problems

How to Clear Rows and Copy a Range Using Excel VBA Macros

Guest Writer Oct 8, 2026 868 views

Question details

The user needs an Excel VBA macro to first clear the contents of a specified number of rows below a certain point, and then copy a designated range downward by a dynamic number of rows based on specific cell input.

How to Clear Rows and Copy a Range Using Excel VBA Macros

Product: Excel
Device & OS: not provided
Scenario: Automating the process of clearing old data and dynamically copying a new template or range of rows downward based on user-defined values in a spreadsheet.
Observed behavior: The goal state is to clear a dynamic range starting at B10:W10 (based on the value in cell R2) and use the FillDown method for the B9:W9 range (based on the value in cell B2).

Before you start

Ensure you have saved your workbook as an Excel Macro-Enabled Workbook (.xlsm) and that the Developer tab is enabled in your Excel ribbon to write and execute VBA code.

Solution 1Recommended

Create and Run the Clear and Copy VBA Macro

This solution uses a custom VBA script to dynamically read row counts from specific cells, clear the outdated data, and fill down the designated range.

By utilizing the ClearContents and FillDown methods in VBA, you can fully automate repetitive data refreshing tasks. This macro reads the variables from cells R2 and B2 so that you don't have to manually update the code each time the required row count changes.

Open the VBA Editor

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

Insert a New Module

Click on 'Insert' in the top menu and select 'Module' to open a new blank coding window.

Paste the VBA Code

Copy and paste the following code into the module window: Sub CopyAndClearRange() Dim clearRows As Long Dim copyRows As Long clearRows = Range("R2").Value copyRows = Range("B2").Value Range("B10:W" & 9 + clearRows).ClearContents Range("B9:W9").Resize(copyRows).FillDown End Sub

Assign the Macro to a Button

Return to your worksheet, navigate to the Developer tab, click 'Insert' > 'Button (Form Control)', draw the button on your sheet, and assign the 'CopyAndClearRange' macro to it for easy execution.

Create and Run the Clear and Copy VBA Macro

Dynamic Automation: By changing the numeric values in cells R2 (rows to clear) and B2 (rows to copy), the macro automatically adjusts its execution scope without requiring any code modifications.

WPS Spreadsheet Automation

Run and Manage VBA Macros Seamlessly in WPS Office

WPS Spreadsheet provides robust support for VBA macros, allowing you to easily automate data tasks like clearing and filling dynamic row ranges. Its highly compatible Visual Basic Editor ensures your existing Excel macros run flawlessly.

1. Enable Developer Tools: Open WPS Spreadsheet, navigate to the options menu, and ensure the Developer Tools tab is enabled.
2. Launch the VBA Editor: Click 'Visual Basic' on the Developer tab or press Alt + F11 to launch the integrated code editor.
3. Execute Your Script: Insert a new module, paste your VBA code, and easily assign it to a form control button on your spreadsheet.

Fully compatible with Microsoft Excel .xlsm and .xlsb macro formatsBuilt-in Visual Basic Editor for writing, testing, and debugging macrosFamiliar user interface requiring zero learning curveLightweight architecture for fast execution of complex automated tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a 'Type Mismatch' error when running this VBA macro?

This error typically occurs if the cells referenced in the code (R2 or B2) contain text, spaces, or are entirely blank instead of holding valid numeric values. Ensure that both cells contain whole numbers.

Can I run this macro on a specific worksheet automatically?

Yes. By default, this VBA code runs on whichever sheet is currently active. To specify a sheet, prefix the Range objects with the sheet name, such as: Sheets("DataSheet").Range("R2").Value.

How do I ensure my VBA macro is saved correctly?

You must save your file as an Excel Macro-Enabled Workbook. Go to 'File' > 'Save As', and in the file format dropdown menu, select the '.xlsm' extension to prevent losing your VBA code.

Excel & Data Problems