.End(xlDown).Row changes from Excel 2007 to 2010

Question

After updating from Office 2007 to Office 2010, the macros that worked perfectly in Excel 2007 but do not work in 2010. Specifically i recieved a error on this line:

    y = Worksheets("Raw Data").Range("A2").End(xlDown).Row

Ther error is "error 6 Overflow". I have come to realize that it is due to Excel selecting the max number(1048576) of rows in excel that creates the overflow. There is only data in 975 of these rows. In 2007 it only selected the rows with data. I am wondering what has caused the change in the way that code is handled from 2007 to 2010? Has anyone else experienced this?

Answer 1

Be sure that y variable (and other variables referring to row numbers) are declared as Long, like:

Dim y As Long

I could guess that your variables are Integer at the moment.

I could guess that you have possibly migrated your file from .xls into .xlsm in the meantime which could cause some of that problems.

Answer 2

Try below

  With Worksheets("Raw Data")
        y = .Range("A" & .Rows.Count).End(xlUp).Row
    End With