need a large spreadsheet
 

I'm using MS Excel 2000 (9.0.3821 SR-1) and I need far more rows than
the 65,536 that I think it allows. I only need about 14 columns if that
helps. Can I increase this number of rows? I'm thinking 150,000 to
200,000?

need a large spreadsheet
 

You would have to use the beta version of XL 2007 if you want more rows.

Otherwise you would need to use a database, such as Access.;
--
Brevity is the soul of wit.


"cj" wrote:

I'm using MS Excel 2000 (9.0.3821 SR-1) and I need far more rows than
the 65,536 that I think it allows. I only need about 14 columns if that
helps. Can I increase this number of rows? I'm thinking 150,000 to
200,000?

need a large spreadsheet
 

How about more sheets? Can you just add sheets?

"Dave F" wrote:

You would have to use the beta version of XL 2007 if you want more rows.

Otherwise you would need to use a database, such as Access.;
--
Brevity is the soul of wit.


"cj" wrote:

I'm using MS Excel 2000 (9.0.3821 SR-1) and I need far more rows than
the 65,536 that I think it allows. I only need about 14 columns if that
helps. Can I increase this number of rows? I'm thinking 150,000 to
200,000?

need a large spreadsheet
 

Yeah just right click in the tab and select insert sheet. Limited only by
available memory.
--
Brevity is the soul of wit.


"JLGWhiz" wrote:

How about more sheets? Can you just add sheets?

"Dave F" wrote:

You would have to use the beta version of XL 2007 if you want more rows.

Otherwise you would need to use a database, such as Access.;
--
Brevity is the soul of wit.


"cj" wrote:

I'm using MS Excel 2000 (9.0.3821 SR-1) and I need far more rows than
the 65,536 that I think it allows. I only need about 14 columns if that
helps. Can I increase this number of rows? I'm thinking 150,000 to
200,000?

need a large spreadsheet
 

If you can use macros, this one will automatically import very large files automatically creating
new worksheets as the 65,536 limit

Thanks go to:

Bernie MS Excel MVP

Sub LargeFileImport()
'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
'Ask User for File's Name
FileName = Application.GetOpenFilename
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FileName For Input As #FileNum
'Turn Screen Updating Off
Application.ScreenUpdating = False
'Create A New WorkBook With One Worksheet In It
Workbooks.Add Template:=xlWorksheet
'Set The Counter to 1
Counter = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & FileName
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr
End If

If ActiveCell.Row = 65536 Then
'If On The Last Row Then Add A New Sheet
ActiveWorkbook.Sheets.Add
Else
'If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If
'Increment the Counter By 1
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop
'Close The Open Text File
Close
'Remove Message From Status Bar
Application.StatusBar = False
End Sub


***************************************
HTH EagleOne



cj wrote:

I'm using MS Excel 2000 (9.0.3821 SR-1) and I need far more rows than
the 65,536 that I think it allows. I only need about 14 columns if that
helps. Can I increase this number of rows? I'm thinking 150,000 to
200,000?

need a large spreadsheet
 

Yes, that's the plan now, but it would make sorting the spreadsheet
difficult if not impossible. Still I'm told sorting isn't an issue.

Dave F wrote:
Yeah just right click in the tab and select insert sheet. Limited only by
available memory.

need a large spreadsheet
 

No, cause it's an .RTF report file that will be brought into Excel via a
VB.Net program so the accountants can play with the data.

wrote:
If you can use macros, this one will automatically import very large files automatically creating
new worksheets as the 65,536 limit

Thanks go to:

Bernie MS Excel MVP

Sub LargeFileImport()
'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
'Ask User for File's Name
FileName = Application.GetOpenFilename
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FileName For Input As #FileNum
'Turn Screen Updating Off
Application.ScreenUpdating = False
'Create A New WorkBook With One Worksheet In It
Workbooks.Add Template:=xlWorksheet
'Set The Counter to 1
Counter = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & FileName
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr
End If

If ActiveCell.Row = 65536 Then
'If On The Last Row Then Add A New Sheet
ActiveWorkbook.Sheets.Add
Else
'If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If
'Increment the Counter By 1
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop
'Close The Open Text File
Close
'Remove Message From Status Bar
Application.StatusBar = False
End Sub


***************************************
HTH EagleOne



cj wrote:

I'm using MS Excel 2000 (9.0.3821 SR-1) and I need far more rows than
the 65,536 that I think it allows. I only need about 14 columns if that
helps. Can I increase this number of rows? I'm thinking 150,000 to
200,000?