VBA & Macro Problems

How to Change a VBA COUNTA Range from Columns B:K to B:M in Excel

Steve K Oct 12, 2026 869 views

Question details

The user needs to update an existing VBA macro so that the row check using COUNTA evaluates data across expanded columns (B through M), rather than stopping at column K.

How to Change a VBA COUNTA Range from Columns B:K to B:M in Excel

Product: Excel
Device & OS: not provided
Scenario: Updating a macro to detect data in additional columns so the script executes accurately for the newly expanded dataset.
Observed behavior: The current macro only checks for data up to column K, failing to detect or process any potential data located in columns L and M.

Before you start

Before modifying the script, verify which specific worksheet the macro runs on and ensure that columns L and M do not contain unrelated calculations or data that might falsely trigger the COUNTA row check.

Solution 1Recommended

Update the Range Reference in the WorksheetFunction.CountA Loop

Modify the existing loop condition within the VBA Editor to check the expanded column range by updating the alphanumeric range reference.

By directly modifying the hardcoded column letters in your WorksheetFunction string, you instruct the macro to evaluate a wider area on the worksheet.

It is crucial to correctly format the concatenation characters (&) to avoid syntax errors.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor, and locate the module containing your specific macro.

Locate the COUNTA Function

Scroll through your code to find the line utilizing the `WorksheetFunction.CountA` method, which should currently contain `Range("B" & i & ":K" & i)`.

Modify the Column Reference

Change the letter K to M in the condition. The updated line should read: `If WorksheetFunction.CountA(Range("B" & i & ":M" & i)) > 0 Then`.

Run and Test

Save your code and press F5 or run the macro from your worksheet to ensure it now successfully detects data spanning up to column M.

Update the Range Reference in the WorksheetFunction.CountA Loop

Qualify Your Worksheet: If your macro might be triggered while a different sheet is active, ensure you explicitly qualify the worksheet to prevent errors (e.g., `Sheets("Sheet1").Range("B" & i & ":M" & i)`).

Solution 2

Use an Alternative Fill-Down and Clear Method

Instead of looping through rows with COUNTA, you can apply a FillDown method to the expanded range and clear empty rows afterward to improve macro execution speed.

Powerful Macro Support

Create and Edit Macros Easily in WPS Spreadsheet

WPS Spreadsheet provides excellent built-in support for VBA macros. You can easily edit your scripts, manage range references like COUNTA, and automate your complex spreadsheet tasks in a familiar environment.

1. Open Your Workbook: Launch WPS Spreadsheet and open your macro-enabled (.xlsm) file.
2. Access the Developer Tab: Navigate to the 'Developer' tab on the top ribbon menu.
3. Launch the VBA Editor: Click the 'Visual Basic' icon to open the integrated code editor.
4. Edit and Execute: Locate your script, modify your COUNTA column ranges, and click 'Run' to test your updated automation natively.

Full support for VBA macro creation and executionHigh compatibility with Microsoft Excel .xlsm and .xlsb formatsLightweight architecture that runs smoothly even with large datasetsClean, intuitive developer interface for debugging code

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA COUNTA returning 0 even when there is data in the row?

This typically occurs if the range reference is pointing to the wrong worksheet. If the macro is triggered from another sheet, unqualified ranges (like `Range(...)` instead of `Sheets("Data").Range(...)`) will evaluate the active sheet, which might be blank.

How do I dynamically set the column range in VBA without hardcoding letters?

You can use the `Cells` property instead of `Range`. For example, `Range(Cells(i, 2), Cells(i, LastCol))` allows you to use a variable (`LastCol`) for the column index, making the script fully dynamic.

Can I use COUNTA to check an entire row in VBA?

Yes, you can check an entire row by using `WorksheetFunction.CountA(Rows(i))`. However, restricting the check to specific columns (like B:M) is better for performance and prevents false positives from stray data in distant columns.

Does WPS Spreadsheet fully support Excel VBA macros?

Yes, the advanced versions of WPS Office fully support VBA. You can run, edit, and debug most Microsoft Excel macros, including custom WorksheetFunction scripts, with high compatibility.

Excel & Data Problems