Adding rows past 65,500

cornbread · Apr 3rd, 2002, 07:58 AM

Once I hit the upper limit of rows is there a way to add more?

Mark W. · Apr 3rd, 2002, 08:00 AM

No -- 65,536 is the row limit for a worksheet.

Ian Mac · Apr 3rd, 2002, 08:01 AM

Simply put NO! Sorry

Check the help files for: limits in Microsoft Excel

cornbread · Apr 3rd, 2002, 08:07 AM

Thanks guys. I will find an alternative way to list the data then.

Jack in the UK · Apr 3rd, 2002, 03:53 PM

Add options

If over 65536 suggest looking at proper database ie Access and pull data into Excel as needed or required.

Also suggest splitting over more than just the one sheet...

any help?

BTW only Spreadsheet i know more than 65536 are SUN and Loyus 123 have a million rows.....

swaink · Apr 5th, 2002, 01:06 PM

cornbread

Try this I can't remember where I got it but when I do Ill publish the author because he deserves a mention, the macro basically creates a new sheet once it reaches the maximum no of rows, please ignore the lines prefixed with an * they are other macros I launch from this one. hope it helps

Kevin

Sub ImportBigText()
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
FileName = InputBox("Please enter the Text File's name, e.g. test.txt")
'If Error Then End
If FileName = "" Then End
FileNum = FreeFile()
Open FileName For Input As #FileNum
Application.ScreenUpdating = False

Workbooks.Add template:=xlWorksheet
Counter = 1
Do While Seek(FileNum) < LOF(FileNum)

Application.StatusBar = "Importing Row " & _
Counter & " of text file " & FileName
Line Input #FileNum, ResultStr
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
* EditTheHeader
* InsertColandCalcQty
* WorkTimes
* RemoveCharatEOF
* ClrSpaces
Else
ActiveCell.Value = ResultStr
End If

If ActiveCell.Row = 65536 Then
ActiveWorkbook.Sheets.Add
Else
ActiveCell.Offset(1, 0).Select
End If
Counter = Counter + 1
Loop

Close
Application.StatusBar = False

End
End Sub

[ This Message was edited by: swaink on 2002-04-05 12:10 ]

wcassell · Apr 5th, 2002, 01:30 PM

In addition to UK Jack's response, if you store the data in an Access Database(or Oracle, DB2, etc), you can create a pivot table that uses ODBC to link to the DB. This way your pivot table has access to the entire data set. If this is a direction you'd like to go, just post any questions.

Will

[ This Message was edited by: wcassell on 2002-04-05 12:30 ]

Apr 3rd, 2002, 08:01 AM	#3
Ian Mac MrExcel MVP Join Date: Feb 2002 Location: Newcastle, UK Posts: 1,174	Simply put NO! Sorry Check the help files for: limits in Microsoft Excel __________________ "Have a good time......all the time" Ian Mac

Apr 3rd, 2002, 03:53 PM	#5
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,065	Add options If over 65536 suggest looking at proper database ie Access and pull data into Excel as needed or required. Also suggest splitting over more than just the one sheet... any help? BTW only Spreadsheet i know more than 65536 are SUN and Loyus 123 have a million rows..... __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 3rd, 2002, 07:58 AM	#1
cornbread Board Regular Join Date: Apr 2002 Location: Oregon Posts: 77	Once I hit the upper limit of rows is there a way to add more?

Apr 3rd, 2002, 08:00 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	No -- 65,536 is the row limit for a worksheet.

Apr 3rd, 2002, 08:07 AM	#4
cornbread Board Regular Join Date: Apr 2002 Location: Oregon Posts: 77	Thanks guys. I will find an alternative way to list the data then.

Apr 5th, 2002, 01:06 PM	#6
swaink Board Regular Join Date: Feb 2002 Location: South UK Posts: 344	cornbread Try this I can't remember where I got it but when I do Ill publish the author because he deserves a mention, the macro basically creates a new sheet once it reaches the maximum no of rows, please ignore the lines prefixed with an * they are other macros I launch from this one. hope it helps Kevin Sub ImportBigText() Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double FileName = InputBox("Please enter the Text File's name, e.g. test.txt") 'If Error Then End If FileName = "" Then End FileNum = FreeFile() Open FileName For Input As #FileNum Application.ScreenUpdating = False Workbooks.Add template:=xlWorksheet Counter = 1 Do While Seek(FileNum) < LOF(FileNum) Application.StatusBar = "Importing Row " & _ Counter & " of text file " & FileName Line Input #FileNum, ResultStr If Left(ResultStr, 1) = "=" Then ActiveCell.Value = "'" & ResultStr * EditTheHeader * InsertColandCalcQty * WorkTimes * RemoveCharatEOF * ClrSpaces Else ActiveCell.Value = ResultStr End If If ActiveCell.Row = 65536 Then ActiveWorkbook.Sheets.Add Else ActiveCell.Offset(1, 0).Select End If Counter = Counter + 1 Loop Close Application.StatusBar = False End End Sub [ This Message was edited by: swaink on 2002-04-05 12:10 ]

Apr 5th, 2002, 01:30 PM	#7
wcassell New Member Join Date: Mar 2002 Posts: 24	In addition to UK Jack's response, if you store the data in an Access Database(or Oracle, DB2, etc), you can create a pivot table that uses ODBC to link to the DB. This way your pivot table has access to the entire data set. If this is a direction you'd like to go, just post any questions. Will [ This Message was edited by: wcassell on 2002-04-05 12:30 ]