VBA & Macro Problems

How to Auto-Clear Cells When a Column Says Yes Using VBA in Excel

Maira Mehtab Sep 28, 2026 869 views

Question details

The user wants a streamlined VBA macro that automatically clears cells in columns H through K for rows 3 to 30 whenever the value "Yes" is entered in column K.

Product: Spreadsheets (VBA)
Device & OS: not provided
Scenario: Automating data clearing tasks dynamically based on user input without having to write separate macro lines for every single row.
Observed behavior: The user requires an efficient event-driven solution to replace repetitive manual code targeting multiple rows individually.

Before you start

Ensure that macros are enabled in your spreadsheet software and that your workbook is saved as a Macro-Enabled Workbook (.xlsm) to prevent losing your VBA code.

Solution 1Recommended

Use Worksheet_Change Event with Intersect and Loop

This method uses an event handler to automatically trigger the macro whenever a cell in column K is modified, checking if it equals 'Yes' and clearing the corresponding cells.

By utilizing the Worksheet_Change event, the macro runs automatically upon data entry in real-time. The Intersect function efficiently restricts the macro to only execute when changes occur within the target range (K3:K30).

Open the VBA Editor

Press 'Alt + F11' on your keyboard to open the Visual Basic for Applications (VBA) editor.

Select the Target Worksheet

In the Project Explorer pane on the left, double-click the specific worksheet (e.g., Sheet1) where you want this data-clearing action to occur.

Paste the VBA Code

Copy and paste the following code into the code window: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim cel As Range Set rng = Intersect(Range("K3:K30"), Target) If Not rng Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False For Each cel In rng If cel.Value = "Yes" Then cel.Offset(0, -3).Resize(1, 4).ClearContents End If Next cel Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub

Save and Test

Close the VBA editor and type 'Yes' in any cell from K3 to K30 in your worksheet. The cells in columns H through K of that row will automatically clear.

Application.EnableEvents Caution: Turning off Application.EnableEvents prevents the macro from repeatedly triggering itself in an infinite loop when it clears the cells. Ensure the code always turns it back on at the end of the script.

Use WPS Spreadsheet Macros

Automate Workflows Easily with WPS Spreadsheets

WPS Spreadsheets provides robust support for VBA and macros, allowing you to automate repetitive tasks like clearing cells based on specific criteria. It is fully compatible with Excel VBA, meaning the exact same macro code works seamlessly.

1. Download WPS Office: Install WPS Office and open your workbook in WPS Spreadsheets.
2. Open Macro Editor: Navigate to the Developer tab on the ribbon and click on the 'Visual Basic' icon to open the VBA editor.
3. Run VBA Code: Paste your Worksheet_Change code directly into the sheet module and it will function exactly as expected.

Full compatibility with Microsoft Excel VBA macro codeLightweight application that runs smoothly on older devicesAdvanced data analysis and automation features for freeFamiliar ribbon interface for a seamless migration experience

microsoft office alternative - wps office

Frequently Asked Questions

Why is my Worksheet_Change macro not triggering when I type Yes?

This usually happens if 'Application.EnableEvents' was turned off by a previous error and not turned back on, or if macros are disabled in your security settings. You can reset events by running 'Application.EnableEvents = True' in the VBA Immediate Window.

How can I change the range to apply the macro to the entire column K?

To apply the macro to the entire column instead of just rows 3 to 30, change 'Range("K3:K30")' to 'Range("K:K")' or 'Columns("K")' inside the Intersect function.

Can I make the 'Yes' condition case-insensitive?

Yes. You can convert the cell value to uppercase before comparing it by modifying the condition to: 'If UCase(cel.Value) = "YES" Then'. This will clear cells whether you type 'Yes', 'YES', or 'yes'.

What does cel.Offset(0, -3).Resize(1, 4) actually do in the code?

The 'Offset(0, -3)' property moves the active selection 0 rows down and 3 columns to the left (from column K back to column H). The 'Resize(1, 4)' property then expands that selection to be 1 row high and 4 columns wide, effectively selecting cells H, I, J, and K for that specific row to be cleared.

Excel & Data Problems