VBA overflow error after upgrading from 2007 to 2010

Question

I have a VBA macro that worked correctly in Excel 2007, but after updating to 2010, it causes errors. A macro basically copies raw data from one sheet to several sheets. Error 6 due to overflow. The line that throws the error is Dim

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

I initially thought it was okay to redo it to the end, and it would kill the overflow error. Well, I think he killed the error, but also returned very incorrect results, and then added that the overflow error didn’t even make sense ... just 973 lines.

Then I thought it might be better to try this instead

 Cells(Rows.Count,"A").End(xlUp).Offset(1,0).Select 

Now it throws a "Runtime Error" 1004 "method" object range "_global" failed "on the line after.

below is part of the complete code. I suppose this could be an overflow error? Any help is appreciated.

 Dim y As Integer 'y = Worksheets("Raw Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select y = Worksheets("Raw Data").Range("A2").End(xlDown).Row Range("B1:U" & y).Select Selection.Copy Sheets("Yellow Suppliers").Select Range("B2").Select ActiveSheet.Paste Columns("C:E").Select Selection.Delete Shift:=xlToLeft Columns("P:Q").Select Selection.Delete Shift:=xlToLeft Columns("A").ColumnWidth = 2.14 Columns("B").ColumnWidth = 43.43 Columns("C").ColumnWidth = 12.14 Columns("D:O").ColumnWidth = 8 Columns("P").ColumnWidth = 10.14 Rows("1").RowHeight = 15 Rows("2:" & y).RowHeight = 30 Range("B3:B22").Select Selection.Font.Bold = True 

Answer 1

Try to get rid of any Select or Selection in your code. Also make sure y really estimates the expected value of 973 .

 Sub Macro2() Dim rngY as Range '## a variable to calculate the size of rows used in Column A' Dim y As Integer With Worksheets("Raw Data") Set rng = .Range("A2").Resize( _ Application.WorksheetFunction.CountA(.Range("A:A"))) End With y = rngY(rngY.Rows.Count).Row Range("B1:U" & y).Copy Destination:= _ Sheets("Yellow Suppliers").Range("B2") Columns("C:E").Delete Shift:=xlToLeft Columns("P:Q").Delete Shift:=xlToLeft Columns("A").ColumnWidth = 2.14 Columns("B").ColumnWidth = 43.43 Columns("C").ColumnWidth = 12.14 Columns("D:O").ColumnWidth = 8 Columns("P").ColumnWidth = 10.14 Rows("1").RowHeight = 15 Rows("2:" & y).RowHeight = 30 Range("B3:B22").Font.Bold = True End Sub 

Answer 2

Integers in VBA are 16-bit numbers, which means that the largest value they can take is 32767. A spreadsheet may have more rows than newer versions of Excel.

You need to use a long, not an integer. Long 32-bit signed; big enough.

So change Dim y As Integer to Dim y as Long

Actually, I am surprised that it worked in Excel 2003, because if my memory is correct, it has 65536 lines. But try my suggestion as your code is definitely incorrect.