VBA & Macro Problems

How to Clear Excel Rows with Checkboxes and a Macro Button

Nimra Malik Sep 25, 2026 870 views

Question details

The user wants to clear specific row data by checking a box next to a completed task and clicking a central button, allowing the row to be reused.

How to Clear Specific Excel Rows Using Checkboxes and a Macro Button

Product: Excel / WPS Spreadsheets
Device & OS: not provided
Scenario: Managing reusable task or job lists where completed rows need their data cleared quickly without deleting the actual row layout.
Observed behavior: Users currently have to manually select and delete contents for each completed job. The goal is to automate this process via checkboxes and a centralized VBA macro button.

Before you start

Ensure that the Developer tab is enabled in your spreadsheet software so you can insert Form Control checkboxes and write VBA code. Also, save your workbook as a Macro-Enabled Workbook (.xlsm) to ensure your macro code is not lost upon closing.

Solution 1Recommended

Automate Row Clearing with Form Control Checkboxes and a VBA Macro

Link Form Control checkboxes to helper cells and use a VBA macro assigned to a button to clear the contents of all checked rows instantly.

This method relies on linking checkboxes to cells in the same row. When a box is checked, the cell returns TRUE. A VBA script then loops through the rows, checks for the TRUE value, clears the specified data range, and resets the checkbox.

Insert Checkboxes

Go to the Developer tab, click 'Insert', and choose the 'Checkbox' under Form Controls. Place one checkbox in each row you want to manage.

Link Checkboxes to Cells

Right-click each checkbox, select 'Format Control', and set the 'Cell link' to a cell in the same row (for example, column I). The linked cell will show TRUE when checked and FALSE when unchecked.

Write the VBA Macro

Press ALT + F11 to open the VBA Editor. Insert a new Module and paste the following code to loop through your rows (adjusting ranges as needed): Sub Clear() Dim r As Long Application.ScreenUpdating = False For r = 4 To 26 If Range("I" & r).Value = True Then Range("C" & r).Resize(1, 6).ClearContents End If Range("I" & r).Value = False Next r Application.ScreenUpdating = True End Sub

Create the Clear Button

Go back to the Developer tab, insert a 'Button' (Form Control) onto your sheet, and assign the 'Clear' macro you just created to it. Clicking this button will now clear all checked rows.

Automate Row Clearing with Form Control Checkboxes and a VBA Macro

Pro Tip: You can hide the column containing the TRUE/FALSE linked cells (e.g., Column I) or change their text color to white to keep your worksheet looking clean and professional.

Advanced VBA Support

Automate Spreadsheets Effortlessly with WPS Office

WPS Spreadsheets provides robust support for VBA and macros, allowing you to create checkboxes, interactive buttons, and custom scripts exactly like you would in Microsoft Excel.

1. Open Developer Tools: Launch WPS Spreadsheets, navigate to the top ribbon, and click on the 'Developer' tab to access all macro and form control features.
2. Insert Controls: Use the 'Insert' dropdown in the Developer tab to add form checkboxes and buttons directly to your active worksheet.
3. Add Your VBA Code: Click 'Visual Basic' or press ALT + F11 to seamlessly paste your row-clearing macro into the integrated WPS VBA editor.
4. Run and Automate: Assign the macro to your newly created button. Click it to execute the VBA code and clear the selected rows instantly.

Full compatibility with Microsoft Excel macro formats (.xlsm)Built-in Developer tools for inserting Form Controls and ActiveX elementsLightweight application that processes large datasets and macros quicklyFamiliar interface with no learning curve required

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA macro not clearing the correct columns?

Check the `Range.Resize` and column references in your VBA code. For example, `Range("C" & r).Resize(1, 6)` starts at column C and spans 6 columns to the right. Adjust the starting column letter and the resize number to perfectly match your specific table layout.

Will this macro delete the formulas in my cleared rows?

Yes, the `ClearContents` method in VBA removes both static values and formulas in the specified range. If you need to keep formulas intact and only clear user-entered data, you must adjust the VBA code to target only specific, non-formula columns.

Can I use ActiveX checkboxes instead of Form Control checkboxes?

Yes, but ActiveX checkboxes are handled differently in VBA. Instead of checking a linked cell's TRUE/FALSE state, your macro would need to interact with the properties of each OLEObject on the sheet. For simpler list setups, Form Control checkboxes are highly recommended.

How do I hide the TRUE/FALSE text from the linked helper cells?

You can either hide the entire column where the linked cells are located (Right-click the column letter header and select 'Hide'), or change the font text color of those specific cells to match your spreadsheet's background color (typically white).

Excel & Data Problems