Excel VBA script stops working at row 32768

Question

I have a macro that I've been using for quite some time to perform cut/paste/move operations on cells in groups of 6 (Excel 2016). Today I tried using it on what is by far the longest worksheet I've ever tried using it on which has almost 200,000 rows. The scripts gets to an operation which should occur on row 32768 and at that point I get an overflow error. Last night I running this concurrent with a shell script that was putting a real strain on my CPU, but today I ran it with nothing else running and I get the overflow error at the same spot. The portion of the script causing the problem is:

Do Until i > nLastRow
    cellVolume = i - 1
    cellPage = i + 1
    cellItem = cellPage + 1
    .Range("A" & i).Cut Range("B" & cellVolume)
    .Range("A" & cellPage).Cut Range("C" & cellVolume)
    .Range("A" & cellItem).Cut Range("D" & cellVolume)
    i = i + 6
Loop

i is initialized to 2 and in this particular worksheet nLastRow is going to be 193596.

I was watching my CPU usage just now and it never goes above about 63% while this script runs so it shouldn't be an issue of processing power.

I can split this worksheet up into sections as a kludgey fix, but I'd rather have it work properly.

My variables are set as:

Dim i As Long, cellVolume As Integer, cellPage As Integer, cellItem As 
Integer, j As Integer
Dim nLastRow As Long, x As Long
Dim str As String

Any suggestions on how to fix this?

What do you think is special about 32768? https://en.wikibooks.org/wiki/Visual_Basic/Data_Types — n8wrl, Jul 21 '17 at 17:16
32768 is the extent of an integer. when `i` is greater than 32768 it can not be put in cellPage that is an integer. — Scott Craner, Jul 21 '17 at 17:17

score 1 · Answer 1 · edited Jul 21 '17 at 17:46

1

Try:

Dim cellVolume As Long, cellPage As Long, cellItem As Long

edited Jul 21 '17 at 17:46

David Zemens

53,033
11
81
130

answered Jul 21 '17 at 17:22

Pv-Viana

632
7
25

This answer is technically correct, but of low quality. Code-only answers without explanation are not good answers, generally. Further, this question already has an answer (linked as duplicate). – David Zemens Jul 21 '17 at 17:47

Excel VBA script stops working at row 32768

1 Answers1