VBA Loop Count: Limits, Counter Types, and Runtime

VBA has no single built-in maximum number of loop iterations. A loop can keep running as long as its conditions allow, but a For...Next counter is limited by its data type, and real-world runtime, memory, and the work performed usually set the practical limit.

There Is No Fixed VBA Loop Quota

VBA does not impose a universal rule such as a maximum of one million passes per loop. A For...Next loop can run a fixed number of times, while a Do...Loop can continue until a condition changes. The program can also leave either kind of loop early using Exit For or Exit Do.

That does not mean every possible count is useful or achievable. The counter must be capable of representing the values it reaches, and the computer must have time to perform the repeated work. A loop that performs a simple arithmetic operation may run far more iterations in a given time than one that reads or writes individual worksheet cells, opens files, or calls another application.

It also helps to separate the loop’s count from its task. A loop can have a count beyond the number of rows in a worksheet if it is doing other work. Conversely, a loop with only a few thousand passes may be slow if each pass triggers expensive Excel recalculation or interacts with the screen.

The Counter Type Sets a Numeric Boundary

In a For...Next loop, the counter is a numeric variable. Its data type determines which values it can represent; it does not set a recommended number of iterations. Common VBA numeric types have these ranges:

Integer is a signed 16-bit integer, from -32,768 to 32,767. It is rarely a good choice for a loop that might need more than a few tens of thousands of counts.
Long is a signed 32-bit integer, from -2,147,483,648 to 2,147,483,647. It is a common choice for row numbers and ordinary loop counters in VBA.
LongLong is a signed 64-bit integer available in 64-bit VBA. It can represent much larger whole numbers, but it is not available as a general counter type in 32-bit VBA.
Double can represent a much wider range of magnitudes, but it is a floating-point type. It cannot represent every integer exactly at very large values, so it is not automatically a safe substitute for an integer counter.

On both 32-bit and 64-bit Office, VBA’s Long remains 32-bit. Moving to 64-bit Office does not make a Long counter larger. LongPtr is intended for pointer-sized values when calling platform APIs; it is not a general-purpose way to extend a loop’s count.

Choose a counter type that can hold the starting value, ending value, and intermediate values. If a counter reaches its type’s boundary, arithmetic overflow can stop the macro with an error. A wider type changes the representable range, not the time required to complete the work. A loop with billions of passes may be technically representable and still be impractical.

How For…Next Determines the Number of Passes

A For...Next loop starts at its initial value, advances by its Step, and stops when it passes the ending value in the direction of travel. The ending value is not necessarily one of the values used: it is reached only when the step lands on it. With a positive step, the starting value must be at or below the ending value for the loop to run; with a negative step, it must be at or above the ending value.

For example, a loop from 2 through 10 with a step of 2 visits 2, 4, 6, 8, and 10: five passes. A loop from 2 through 9 with the same step visits 2, 4, 6, and 8: four passes. A loop from 10 down to 2 with a step of -2 also makes five passes. These examples show why the ending value alone does not tell you the iteration count.

When the step points toward the endpoint, the number of passes can be calculated as the whole-number part of (ending value minus starting value) divided by the step, plus one. For a negative step, use the corresponding positive distance and step size. This calculation assumes the loop is not exited early and the counter can represent the values involved. A starting value already beyond the endpoint in the step’s direction results in no passes.

Be careful with a zero step: the counter never advances, so the loop may run forever unless something inside the loop changes the control flow. A condition inside the body, an explicit exit, or an external stop may prevent that, but a zero step is usually a mistake rather than a useful loop design.

Excel Worksheet Limits Are Separate

For Excel on Microsoft 365 for Windows, a worksheet has 1,048,576 rows and 16,384 columns. These are worksheet grid limits, not a VBA loop-count limit. A macro can count past the last row if it does not try to address nonexistent worksheet cells, but a loop that uses its counter as a row number must stay within the relevant sheet’s rows.

For example, a loop that processes rows 2 through 100,001 makes 100,000 passes. That is within the worksheet’s row limit, but the code still needs to handle blank rows, headers, and the actual extent of the data correctly. On Excel for Mac, the same basic worksheet grid limits apply; the VBA editor and some keyboard shortcuts differ from Windows, but the numeric range of VBA’s Long does not change.

Before running a macro that changes workbook contents, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z on Windows; on Mac, Command+Z does not undo a macro’s changes either. The sample below illustrates the count only; replace the comment with the intended operation.

Option Explicit

Sub VisitRows()
    Dim rowNumber As Long

    ' Visit rows 2 through 100001: 100000 passes.
    For rowNumber = 2 To 100001
        ' Put the required row operation here.
    Next rowNumber
End Sub

What Usually Limits a Large Loop in Practice

For spreadsheet macros, time is often the first practical constraint. Reading or writing a cell through the Excel object model on every pass can be much slower than doing the same calculation in memory. Screen updates, automatic recalculation, event handlers, and other work triggered by each change can add further delays. A loop may be valid VBA and still appear unresponsive because Excel has not returned control to the user interface.

Memory matters when a macro stores data as it goes. A loop that updates a few variables has a different memory profile from one that creates a large collection or array on every pass. The counter’s maximum value does not tell you how much memory the entire procedure will consume.

Other factors include the complexity of each pass, workbook size, the number of formulas affected, and whether the macro communicates with external files or applications. In a nested loop, the work can grow especially quickly: if an outer loop runs 1,000 times and an inner loop runs 1,000 times for each outer pass, the body runs 1,000,000 times. Adding another loop with 1,000 passes would raise that to 1,000,000,000 passes.

Estimate the total work before launching a long macro. Test on a saved copy with a small section of the data, confirm the results, then scale up. If a run is taking unexpectedly long, check whether each pass is repeatedly recalculating formulas, selecting cells, or performing an operation that could instead be done once on a range.

Safer Ways to Run Long Loops

Long-running code should have a clear stopping condition and a way to verify progress. For a fixed For...Next loop, inspect the start, end, and step together. For a condition-based loop, make sure the condition can eventually become false. If progress reporting is needed, update it occasionally rather than on every iteration, since frequent screen updates can themselves slow the macro.

Use a counter type that can represent every value the loop will reach.
Check that the step has the right sign and is not zero.
Use Exit For or Exit Do only when the intended early-stop condition is clear.
Test with a small input and verify the output before processing the full dataset.
Save a backup first, particularly when the macro overwrites cells or deletes data.

DoEvents can give Windows and Excel a chance to process pending events during a long procedure, but it does not make the loop faster or add to its numeric range. It can also allow other event-driven code to run while the procedure is in progress, so use it thoughtfully. For a macro that must be interruptible or report progress, design and test that behavior rather than relying on a larger counter to solve the problem.

When to Use Another Loop Pattern

Changing from For...Next to Do While or Do Until does not remove numeric limits, runtime limits, or the need for a termination condition. A For...Next loop is usually clearest when the number of passes is known. A Do loop is useful when the macro should continue until a condition is met, such as finding the first empty row or processing items until there are none left.

For large Excel datasets, the best alternative may be to reduce the number of VBA-level operations rather than change loop syntax. Reading a contiguous range into a VBA array, processing values in memory, and writing results back in a block can avoid repeated communication with individual cells. Excel formulas or built-in features may also handle a transformation more efficiently. The right choice depends on the task: bulk operations are not always suitable when each row requires a different action or external interaction.

Keep the loop aligned with the data you actually need to process. For instance, finding the last used row in the relevant column can avoid scanning a worksheet’s entire grid, but the method must match the workbook’s layout and how it represents empty cells. A short, well-bounded loop over real data is usually easier to maintain and faster than a huge loop that checks every possible row.

Frequently Asked Questions

What is the maximum number of loops in VBA?
There is no one maximum that applies to every VBA loop. The counter’s type sets its numeric range for a For...Next loop, while execution time and the work performed determine what is practical.

Can a VBA For loop run more than 32,767 times?
Yes. That value is the upper boundary of an Integer, not a general limit on VBA loops. Declare a Long counter for counts beyond that range, provided the values remain within the Long range.

Can a VBA loop run more than 2 billion times?
A Long cannot represent every count above its signed 32-bit range. In 64-bit VBA, LongLong has a wider integer range, but billions of iterations may take too long to be useful. Choose the type and design based on the work, not just the largest representable number.

Does 64-bit Excel make VBA loops faster or give Long a bigger range?
It does not widen VBA’s Long; that type remains 32-bit. Performance depends on the procedure and workload, and 64-bit Office mainly changes the platform’s available address space and compatibility considerations, not the basic loop rules.

Why does my VBA loop never stop?
Common causes include a Do loop condition that never changes, a counter that is not advanced, or a zero Step. Check the exit condition and confirm that each pass changes the value or state the condition depends on.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.