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

Answer 1

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

Answer 2

Change all your Integer declarations to Long

Dim Ctr As Long