Run-time error ' 6' overflow: Microsoft Excel VBA

Question

I tried to solve this overflow in my excel sheet and still not able to solve it. I knew that my data is really a lot (more than 32767 rows). please take a look a my code and help me advise on this. Thanks.

This is remove Macro

Sub Remove()
'
' Remove Macro
'
Dim Ctr As Integer
Dim CtrS As String
Dim EOF As Boolean
Dim LstStr As String
Dim CurStr As String

Ctr = 8
LstStr = ""
Cutstr = ""
EOF = False

Do While Not EOF
    CtrS = CStr(Ctr)
    CurStr = Range("A" & CtrS).Value & "-" & _
             Range("B" & CtrS).Value & "-" & _
             Range("C" & CtrS).Value & "-" & _
             Range("D" & CtrS).Value & "-" & _
             Range("E" & CtrS).Value & "-" & _
             Range("F" & CtrS).Value & "-" & _
             Range("G" & CtrS).Value & "-" & _
             Range("H" & CtrS).Value & "-" & _
             Range("I" & CtrS).Value & "-" & _
             Range("J" & CtrS).Value & "-" & _
             Range("K" & CtrS).Value
    If CurStr <> "----------" Then
        If CurStr = LstStr Then
            'Clear content A to N, except L
            Range("A" & CtrS, "L" & CtrS).ClearContents
            Range("N" & CtrS, "O" & CtrS).ClearContents
        Else
            LstStr = CurStr
        End If
    Else
        EOF = True
    End If
    Ctr = Ctr + 1
Loop
'
End Sub

When I tried to debug, the error point to here:

Ctr = Ctr + 1

In VBA an integer is a signed 16 bit value for compatibility with VB4 16 bit code. Declare as Long for a 32 bit signed integer (+/- 2 billion). — user6017774
– user6017774, Commented Nov 8, 2016 at 6:43
You probably have the data that exceeds integer type limit, change to long as @Tim Williams says. — Tedo G.
– Tedo G., Commented Nov 8, 2016 at 6:44
@TedoG.Thz..Already followed his answer and problem solved it. — eagle eagle
– eagle eagle, Commented Nov 8, 2016 at 7:53

Tim Williams · Accepted Answer · 2016-11-08 06:42:40Z

2

Change all your Integer declarations to Long

Dim Ctr As Long

answered Nov 8, 2016 at 6:42

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

eagle eagle Over a year ago

Thanks for the answer. Problem Solved.

user3598756 · Accepted Answer · 2016-11-08 07:27:26Z

you've been already been given the answer

you may also consider the following refcatoring of your code:

Option Explicit

Sub Remove()
    '
    ' Remove Macro
    '
    Dim Ctr As Long
    Dim EOF As Boolean
    Dim LstStr As String
    Dim CurStr As String

    Ctr = 8
    Do
        CurStr = Join(Application.Transpose(Application.Transpose(Range("A" & Ctr).Resize(, 11).Value)), "-")
        If CurStr <> "----------" Then
            If CurStr = LstStr Then
                'Clear content A to N, except L
                Range("A" & Ctr & ":L" & Ctr & ",N" & Ctr & ":O" & Ctr).ClearContents
            Else
                LstStr = CurStr
            End If
        Else
            EOF = True
        End If
        Ctr = Ctr + 1
    Loop While Not EOF
End Sub

where

Option Explicit

would have spotted that Cutstr = "" typo (it should have been Curstr = "")
no need for
```
Dim CtrS As String
```
and all its subsequent initialization and use

since Ctr variable of Long type will be implicitly cast to a String when combined to another String variable through the & operator
no need for
```
LstStr = ""
Cutstr = ""
```
since String variables are always initialized to an empty one at their declaration
no need for
```
EOF = False
```
since Boolean variables are always initialized to False at their declaration
While Not EOF

moved to the Loop part of the Do - Loop block

since EOF first value is always False
change
```
CurStr = Range("A" & CtrS).Value & "-" & _
         Range("B" & CtrS).Value & "-" & _
         ...
         Range("K" & CtrS).Value
```
to
```
CurStr = Join(Application.Transpose(Application.Transpose(Range("A" & Ctr).Resize(, 11).Value)), "-")
```
where you take advantage of the Resize() property of Range object and the Join() function

the double Application.Transpose() is there to return a one dimensional array since:
- Range("A" & Ctr).Resize(, 11).Value
  
  would return a two dimensional array of 1 row and 11 columns
- Application.Transpose(Range("A" & Ctr).Resize(, 11).Value))
  
  would return a 2 dimensional array of 11 rows and 1 columns
- Application.Transpose(Application.Transpose(Range("A" & Ctr).Resize(, 11).Value)))
  
  would finally return a 1 dimensional array of 11 elements
Range("A" & Ctr & ":L" & Ctr & ",N" & Ctr & ":O" & Ctr)

takes advantage of one of possible Range() arguments specifications, specifically
```
Range("firstRangeAddress, secondRangeAddress"")
```
where that comma between the two addresses acts as a Union() operator

Collectives™ on Stack Overflow

Run-time error ' 6' overflow: Microsoft Excel VBA

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related