Strange Behaviour: Run Time Error Application-defined or object-defined error

[marc005]

Open a macroenabled excel workbook and then run:

Public Sub wrt()

Worksheets("Sheet1").Select
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
Cells(1, 1).Value = Replace(ActiveSheet.Cells(1, 10000).Address(0, 0), ActiveCell.Row, "")

End Sub

Everything works as it should. Now add another zero to 10000:

Public Sub wrt()

Worksheets("Sheet1").Select
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
Cells(1, 1).Value = Replace(ActiveSheet.Cells(1, 100000).Address(0, 0), ActiveCell.Row, "")

End Sub


Run Time Error
Application-defined or object-defined error

 

[njimack]

Excel worksheets have 16,000 columns (or 264 prior to Excel 2007). You're trying to reference column 100,000!

 

[S.H.A.D.O.]

I think you have got the columns confused with the rows.
You are trying to use 100,000 columns which there aren't.
I hope this helps!

 

[marc005]

16 000 columns seems a bit low! Is it 16 000 even for excel 2010?
What I am trying to do is to transpose 650 000 rows in to 650 000 columns ha ha

Excel runs into trouble already at:

Lastrow = Range("A" & Rows.Count).End(xlUp).Row
Cells(1, Lastrow).Select
ColLetter = Replace(ActiveCell.Address(0, 0), ActiveCell.Row, "")


I might have to do my modeling elsewhere

 

[S.H.A.D.O.]

If you hold down the Ctrl key and press the Right Arrow Key, that will take you to the last column in your SpreadSheet. You can work out how many columns you have from there, it should be 16,384!

I hope this helps!