VBA Integer Overflow at 70,000

Question

I have a worksheet with 70,000 rows of data and a simple macro that counts the total number of rows:

Dim LastRow as Integer    
LastRow = SourceSheet.Cells.Find(what:="*", searchdirection:=xlPrevious, searchorder:=xlByRows).Row
MsgBox (LastRow)

This returns an overflow error. Changing LastRow to Long returns the correct value of 70,000.

According to Microsoft's website, a VBA integer should be able to hold values between -2,147,483,648 and 2,147,483,647. My output is well within this range, so why would it fail?

Thanks!

MatthewD · Accepted Answer · 2019-06-15 23:31:45Z

32

The website you are looking at is for Visual Studio (.NET) .

VBA Integer is 32,768. You need to use a long.

A VBA Long variable holds whole numbers from -2,147,483,648 to 2,147,483,647 and uses 4 bytes (32 bits) of memory.

Dim lLastRow as Long

edited Jun 15, 2019 at 23:31

answered Jul 15, 2015 at 16:51

MatthewD

6,8215 gold badges24 silver badges44 bronze badges

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

4 Comments

DixieFlatline Over a year ago

Gah! Of course I was looking at the wrong page. Thanks for the quick answer. +1

MatthewD Over a year ago

@DixieFlatline Just an FYI, You can also access the last row at anytime by SourceSheet.UsedRange.Rows.Count and the last column by SourceSheet.UsedRange.Columns.Count

DixieFlatline Over a year ago

Thanks for the tip! I compared that with the method I was using and I noticed that mine only counts rows with data, and yours also counts rows with formatting.

Mathieu Guindon Over a year ago

@DixieFlatline Actually UsedRange isn't reliable at all for that

Collectives™ on Stack Overflow

VBA Integer Overflow at 70,000

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related