Row limit in Excel
 

I have around 75000 rows of data to be displayed in Excel sheet, but the
data is getting truncated after 65536 rows because of the limit on
number of rows that Excel allows. Is it possible to remove the row
limit in the excel? or is it possible to add a new worksheet
dynamically whenever the first worksheet is full with 65536 rows?

I am getting this large amount of data when I ran a nVision Report for
Peoplesoft. In this, it runs a Query and outputs the result data to an
excel sheet.

Thanks for any help in advance.

-Mann.


--
mannan
------------------------------------------------------------------------
mannan's Profile: http://www.excelforum.com/member.php...o&userid=33856
View this thread: http://www.excelforum.com/showthread...hreadid=536319

Row limit in Excel
 

Office 2007 will have 1M+ rows, but you cannot add more than 65336 in
current versions.

You will need to spread it over multiple worksheets, and manage that
manually or with a macro (there is no event that knows it has been filled,
but you could use the change event and check for row 65536), or perhaps
store the data in Access and use Excel to pull summarised data.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mannan" wrote in
message ...

I have around 75000 rows of data to be displayed in Excel sheet, but the
data is getting truncated after 65536 rows because of the limit on
number of rows that Excel allows. Is it possible to remove the row
limit in the excel? or is it possible to add a new worksheet
dynamically whenever the first worksheet is full with 65536 rows?

I am getting this large amount of data when I ran a nVision Report for
Peoplesoft. In this, it runs a Query and outputs the result data to an
excel sheet.

Thanks for any help in advance.

-Mann.


--
mannan
------------------------------------------------------------------------
mannan's Profile:
http://www.excelforum.com/member.php...o&userid=33856
View this thread: http://www.excelforum.com/showthread...hreadid=536319

Row limit in Excel
 

Thanks Bob for your reply.

To spread the data over multiple worksheets, How can we add multiple
worksheet programatically/dynamically? Also, to check if the worksheet
is filled, you mentioned to "use *Change Event* and check for
row65536". Can you explain briefly, how to use Change Event?

Have a Good Day.


--
mannan
------------------------------------------------------------------------
mannan's Profile: http://www.excelforum.com/member.php...o&userid=33856
View this thread: http://www.excelforum.com/showthread...hreadid=536319

Row limit in Excel
 

Like this, which automatically adds a new worksheet

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Row = Me.Rows.Count Then
Worksheets.Add
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mannan" wrote in
message ...

Thanks Bob for your reply.

To spread the data over multiple worksheets, How can we add multiple
worksheet programatically/dynamically? Also, to check if the worksheet
is filled, you mentioned to "use *Change Event* and check for
row65536". Can you explain briefly, how to use Change Event?

Have a Good Day.


--
mannan
------------------------------------------------------------------------
mannan's Profile:
http://www.excelforum.com/member.php...o&userid=33856
View this thread: http://www.excelforum.com/showthread...hreadid=536319