VBA & Macro Problems

How to Create an Excel VBA Function to Count Consecutive Days

Rana GarciaRana Garcia Nov 2, 2026 871 views

Question details

The user needs a custom function to count consecutive non-blank or non-zero days, where groups of 1 to 4 consecutive days count as 1, and groups of 5 or more count according to their actual length.

Create an Excel VBA Function to Count Consecutive Groups of Days
Product
Excel
Device & OS
not provided
Scenario
Tracking consecutive active days in a spreadsheet where different grouping and counting rules apply based on the length of the streak.
Observed behavior
Standard Excel formulas cannot easily handle this complex conditional counting, prompting the need for a User-Defined Function (UDF) in VBA.
Before you start

Ensure you save your workbook as a Macro-Enabled Workbook (.xlsm) to prevent losing your custom VBA functions upon closing the file.

Solution 1Recommended

Use a Custom VBA Function to Count Groups

Create a User-Defined Function (UDF) using VBA that loops through a selected range and applies your specific grouping logic based on consecutive non-blank cells.

This VBA function counts each consecutive group of non-blank cells. It evaluates the streak and adds 1 if the streak is between 1 and 4 days, or adds the actual number of days if the streak is 5 or more.

1
Open the VBA Editor

In Excel, press the Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.

2
Insert a New Module

Click on 'Insert' in the top menu bar, then select 'Module' to create a blank script window.

3
Paste the VBA Code

Copy and paste the following code into the module window: Function CountGroups(rng As Range) As Long Dim i As Long, n As Long For i = 1 To rng.Count If rng(i).Value <> "" Then n = n + 1 If i = rng.Count Or rng(i).Value = "" Then Select Case n Case 0 Case 1 To 4: CountGroups = CountGroups + 1 Case Else: CountGroups = CountGroups + n End Select n = 0 End If Next i End Function

4
Apply the Formula

Close the VBA editor. In your worksheet, select the cell where you want the result, type `=CountGroups(A1:G1)` (adjusting the range as needed), and press Enter. You can then drag the fill handle to copy the formula down.

Use a Custom VBA Function to Count Groups
Function Execution: Your custom function will now evaluate streaks properly. There is no need to use Ctrl+Shift+Enter as this is treated like a standard formula.
Advanced Data Analysis Tool

Easily Run VBA Macros with WPS Spreadsheet

WPS Office offers robust support for VBA macros, allowing you to create and run custom functions like CountGroups seamlessly. It provides a familiar interface for Excel users to handle complex data tasks effortlessly.

  1. 1. Install WPS Office: Download and install WPS Office, ensuring you select the package that includes VBA support.
  2. 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing .xlsm or .xlsx file.
  3. 3. Access the Macro Editor: Navigate to the Developer tab and click on 'Macro Editor' or simply press Alt + F11 to insert your custom function code.
  4. 4. Use the Function: Save the module and use `=CountGroups()` directly in any cell to calculate your consecutive data streaks.
Fully compatible with Microsoft Excel .xlsm and .xlsx formatsBuilt-in VBA editor for running complex custom macrosLightweight and fast performance for analyzing large datasetsFree to download with an intuitive user interface
microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a #NAME? error when using my custom VBA function?

This usually happens if the macro code is placed in the wrong module type (it must be in a standard module, not a Sheet module), if macros are disabled in your security settings, or if there is a spelling mistake in the function name.

How can I modify the VBA function to count values greater than zero instead of non-blank cells?

You can change the condition in the VBA script from `If rng(i).Value <> ""` to `If IsNumeric(rng(i).Value) And rng(i).Value > 0`. Also, modify the reset condition to check for zero or empty values.

Do I need to use Ctrl+Shift+Enter for this custom function?

No. Custom VBA functions (User Defined Functions) like CountGroups process the range internally and return a single value. You just need to press Enter normally.

How do I save an Excel file that contains custom VBA functions?

You must save the file as an Excel Macro-Enabled Workbook (.xlsm) or an Excel Binary Workbook (.xlsb). Saving it as a standard .xlsx file will permanently strip the VBA code from the workbook.