CODEWITHSUNDEEP
vbaexcel
offcenter35
offcenter35

Reputation: 145

How to automate VBA macro to run through entire file?

I have a VBA Macro that needs to run through a very large Excel file (about 550,000 rows).

I have found that the Macro can only run through about 20,000 lines at a time - any bigger than that and it gives an 'Overflow' error.

My only solution right now is to set j (row number) = 1 to 20,000, run it, then set j from 20,001 to 40,000, run it, then set j from 40,001 to 60,000 etc....all the way to 550,000.

This is a tedious process and I'm wondering is there any way to automate it so that I don't have to keep going back in to change the numbers?

Code looks something like:

Sub ExposedDays()
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim result As String
For j = 2 To 10000
k = 1
Do While Len(Worksheets("my8").Cells(j, k)) > 0
k = k + 1
Loop
Worksheets("my8").Cells(j, 131) = Worksheets("my8").Cells(j, k - 6)
Worksheets("my8").Cells(j, 132) = Worksheets("my8").Cells(j, k - 5)
.......

Upvotes: 0

Views: 158

Answers (1)

Jim Simson
Jim Simson

Reputation: 2872

The integer data type allocates only enough memory to hold integers -32,768 to 32,767. When you:

dim j as integer

an 'Overflow' error will result when j exceeds 32,767. To solve this problem you need to assign a larger data type.

The Long data type is large enough to hold integers -2,147,483,648 to 2,147,483,647. If you:

dim j as long dim k as long

you should be able to avoid this error. Hope this helps.

Upvotes: 2

Related Questions