Adding rows past 65,500

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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.....
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top