Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Adding rows past 65,500

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Oregon
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Simply put NO! Sorry

    Check the help files for: limits in Microsoft Excel
    "Have a good time......all the time"
    Ian Mac

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Oregon
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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"

  6. #6
    Board Regular swaink's Avatar
    Join Date
    Feb 2002
    Location
    51.421818,-0.977139
    Posts
    432
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •