Overflow Error with VBA Code Recommendations

Question

I am trying to run a macro. The macro looks in each row in column A and if the value is greater than 0 it will copy a range of cells from the same row (M:BT) and paste and transpose that range in the cell in column K of the same row. The code below works but the number of rows change and can be a lot. I need it to loop through until no more data can be found in column A. Keep in mind there will be empty rows. As I said before my code works with a small sample 100 rows but when I did one for like 65,000 rows it give me an

Overflow error

Any ideas how to fix this?

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+w
'
Range("K1").Select
ActiveCell.FormulaR1C1 = "DATE"
Range("L1").Select
ActiveCell.FormulaR1C1 = "DAYS"
Columns("BU:EB").Select
Selection.Delete Shift:=xlToLeft
Columns("EC:IR").Select
Selection.Delete Shift:=xlToLeft
With ActiveSheet
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Dim AddRows As Integer: AddRows = 59

Dim i As Integer: i = lastrow

Do While i <> 1
    Rows(i & ":" & i + AddRows - 1).Insert
    i = i - 1
Loop

ScreenUpdating = True
Rows("2:60").Select
Selection.Delete Shift:=xlUp
Range("A1").Select

Dim x As Integer, a As Integer
a = Cells(Rows.Count, 1).End(xlUp).Row

For x = 2 To a
If Cells(i, 1) > 0 Then
Range(Cells(x, 13), Cells(x, 72)).Select
Selection.Copy
Cells(x, 11).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=True, Transpose:=True
End If
Next x
End Sub

Lord Peter · Accepted Answer · 2017-12-08 18:04:23Z

2

Integers are not "big enough" to hold your large number. Use Long instead of Integer.

Dim x As Long, a As Long

See https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/data-type-summary Integer data type is signed 2 bytes and can therefore only hold from -32768 to + 32767

edited Dec 8, 2017 at 18:04

Lord Peter

3,5012 gold badges36 silver badges35 bronze badges

answered Dec 8, 2017 at 17:23

braX

11.9k5 gold badges24 silver badges38 bronze badges

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

Collectives™ on Stack Overflow

Overflow Error with VBA Code Recommendations

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related