VBA & Macro Problems

How to Generate Invoice Numbers for Variable-Length Rows using Excel VBA

Muhammad Talha Oct 10, 2026 871 views

Question details

The user needs a method to automatically assign sequential invoice numbers to hundreds of thousands of insurance line items, where each invoice spans a variable number of rows and is grouped by the invoice date.

How to Generate Invoice Numbers for Variable-Length Rows Using Excel VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Processing massive datasets of insurance invoice line items that require dynamic grouping and numbering based on the specific invoice date rather than a fixed row count.
Observed behavior: Invoice line items lack dynamically assigned invoice numbers, and manual numbering is impossible due to the variable row lengths and the massive volume of data.

Before you start

Ensure your dataset is accurately sorted by the Invoice Date column (and Policy Number, if applicable) before running any macros. Save a backup of your workbook as a Macro-Enabled Workbook (.xlsm) to prevent accidental data loss during script execution.

Solution 1Recommended

Use a VBA Macro to Scan and Increment Invoice Numbers by Date

This method iterates through your dataset and assigns the same invoice number to consecutive rows sharing the same invoice date, incrementing the number only when the date changes.

Because each invoice can contain a variable number of line items, standard auto-fill sequences will not work. By writing a VBA script, Excel can evaluate the invoice date column dynamically and assign matching invoice numbers for grouped rows automatically.

Open the VBA Editor

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

Insert a New Module

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

Define the Loop and Variables

Write a script declaring a starting invoice number (e.g., InvNum = 1000). Use a 'For...Next' loop to iterate from row 2 to the last used row in your worksheet.

Set the Condition to Increment

Inside the loop, use an 'If' statement to compare the date of the current row with the date of the previous row (e.g., If Cells(i, 2).Value <> Cells(i-1, 2).Value Then InvNum = InvNum + 1). Print the 'InvNum' variable to your dedicated invoice number column.

Run the Macro

Close the VBA editor and press ALT + F8. Select your new macro from the list and click 'Run' to populate hundreds of thousands of rows instantly.

Use a VBA Macro to Scan and Increment Invoice Numbers by Date

Include Policy Numbers in the Logic: If your data contains multiple policies processed on the exact same day, adjust your VBA 'If' condition to check both the Policy Number and Invoice Date columns before deciding to increment the invoice number.

Solution 2

Use an IF Formula to Generate Invoice Numbers (Non-VBA Alternative)

If you prefer not to use macros, you can use a logical IF formula to check if the current row's date matches the row above and increment the number accordingly.

WPS Spreadsheet

Easily Manage Large Datasets and Macros with WPS Spreadsheet

WPS Office provides robust spreadsheet capabilities, including comprehensive support for VBA macros and complex formulas. You can seamlessly process hundreds of thousands of rows and automate variable-length invoice numbering within its highly intuitive interface.

1. Open Your Data: Download and install WPS Office, then open your large invoice dataset in WPS Spreadsheet.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click 'Visual Basic' to launch the integrated macro editor.
3. Run the Invoice Macro: Paste your custom VBA code for generating dynamic invoice numbers into a new module and hit 'Run' to apply it.
4. Save as Macro-Enabled File: Go to 'Menu' > 'Save As' and choose the '.xlsm' format to preserve your VBA script for future use.

Fully compatible with Microsoft Excel macro-enabled files (.xlsm) and standard VBA syntax.Smooth performance when handling massive datasets with hundreds of thousands of rows.Free, lightweight, and user-friendly suite for everyday data processing and automation.

microsoft office alternative - wps office

Frequently Asked Questions

Can I generate invoice numbers based on both policy number and invoice date?

Yes, in your VBA script, you can combine conditions using an 'Or' statement. For example: If Cells(i, DateCol) <> Cells(i-1, DateCol) Or Cells(i, PolicyCol) <> Cells(i-1, PolicyCol) Then. This ensures the invoice number increments when either the policy or the date changes.

Why does my VBA loop stop before reaching the end of the dataset?

This usually happens if your script determines the last row based on a column that contains blank cells. Ensure you define the 'LastRow' variable using a column that is fully populated, such as the Policy Number or Date column, using LastRow = Cells(Rows.Count, 1).End(xlUp).Row.

How do I add a text prefix like 'INV-' to the generated numbers in VBA?

Instead of assigning a raw integer to the cell, you can concatenate a text string with your numeric counter. For example, write your output as: Cells(i, OutputCol).Value = "INV-" & InvNum. Ensure your InvNum variable remains stored as a number behind the scenes so it can still increment properly.

Excel & Data Problems