Updating Row Information for Pasting Values

[rvollmer]

I have an Excel worksheet with approximately 1000 lines and 475 columns (Column A is the 'header' column followed by 474 columns of data). I was asked to 'Restack' the data into a two column format. this would create a worksheet 2 columns wide by approx 474,000 lines long (header information in column 1 with data in column 2). An excerpt of my macro is listed below:
Dim DataCheck As String
Dim LastRow As Integer
-------------------------------------------------------------------
LastRow = Sheets("Restacked Data").Range("A2").Row
Sheets("Working").Activate
Range("D3").Select
DataCheck = ActiveCell.Value

Do Until DataCheck = "End of Data"
'Section of code removed
'Code loops through various columns on the "Working" tab and copies data to be
'pasted in the "Restacked Data" tab

Sheets("Restacked Data").Select
Range("A" & LastRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
LastRow = Sheets("Restacked Data").Range("A2").End(xlDown).Row
LastRow = LastRow + 1

'DataCheck value is updated after each section and continues Loop
'until it reaches final section of data
Loop
---------------------------------------------------------------------------
My problem is the section highlighted in red. My macro successfully loops through the original data and pastes it onto a new tab. It also defines the last row of data in the new worksheet so each subsequent piece of data is pasted immediately below the prior data. However, once the LastRow Value exceeds 33,000, I receive the following error message:

Run-time Error '6': Overflow

Is there a limitation on this type of data? Is there a better way to code this? I'm using Microsoft Excel 2010 with VBA Analysis ToolPak Add-IN. Any help would be appreciated.

[Bob Phillips]

Here's another way


Public Sub RestackData()
Dim Restacked As Worksheet
Dim LastRow As Long
Dim Numcols As Long
Dim NumRows As Long
Dim i As Long

Set Restacked = Worksheets("Restacked Data")
With Worksheets("Working")

NumRows = .Range("A2").End(xlDown).Row - 1
Numcols = .Range("A1").End(xlToRight).Column - 1
Restacked.Range("A1:B1").Value = Array("ID", "Value")
LastRow = 2

For i = 2 To Numcols

.Range("A2").Resize(NumRows).Copy Restacked.Cells(LastRow, "A")
.Cells(2, i).Resize(NumRows).Copy Restacked.Cells(LastRow, "B")

LastRow = LastRow + Numcols
Next i
End With
End Sub

[alansidman]

Change lastrow from Integer to Long in your Dim statement

[SamT]

Always use Longs for Row and Column Counting

[rvollmer]

Thanks! Rookie mistake. Changed to Long and everything ran without issue.