Adding more than 65,536 rows in Excel 2007

[WL]

When I try to cut and insert 20,000 rows from one worksheet to another
worksheet that already has 50,000 rows I get the error "To prevent possible
loss of data, Excel cannot shift nonblank cells off of the worksheet." I
only see a total 65,536 rows in the worksheet - I thought Excel 2007
supported a million rows.

 

[Tyro]

Sounds like you have a pre-Excel 2007 workbook open in Excel 2007 in
compatibility mode (look in the title bar and see if it says compatibility
mode). If so, the workbook has 65,536 rows, not 1,048,576. You can save the
workbook as an Excel workbook which will be in Excel 2007 format, close the
workbook and re-open it.

Tyro

 

[vikas]

Hi,

I am getting error in macro.

To prevent possible loss of data, Microsoft Excel cannot shift nonblank
cells off the worksheet.


Below is code

Sheet1.Range(Cells(1, 8), Cells(NbListClients + 1, 12)) = ListClients
' shriyansh
Sheet1.Rows("1:3999").Insert Shift:=xlDown

i need help on this.

 

[Dave Peterson]

Maybe...

XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns
http://support.microsoft.com/default.aspx?scid=kb;en-ca;211769

Remember to look for comments and merged cells.

And for objects that may not be displayed:
xl2003:
tools|options|view tab|Check Show All

xl2007:
Office button|excel options|advanced
Display options for this workbook|For objects|Show|check all

ctrl-6
Should do the same in any version.


Debra Dalgleish has a blog post, too:
http://blog.contextures.com/archives/2009/03/16/cannot-shift-objects-off-sheet-in-excel-2007/

=============
ps. You have a bug in your code. You'll see it if Sheet1 is not the
activesheet (if the code is in a general module)--or if Sheet1 is not the module
that owns the code.

This line should have the cells() qualified, too:
Sheet1.Range(Cells(1, 8), Cells(NbListClients + 1, 12)) = ListClients

either:
Sheet1.Range(sheet1.Cells(1, 8), sheet1.Cells(NbListClients + 1, 12)) _
= ListClients

or (I like):

with sheet1
.Range(.Cells(1, 8), .Cells(NbListClients + 1, 12)) = ListClients
end with

The leading dots means that that object (.range() or .cells()) belong to the
object in the previous with statement--in this case Sheet1.

 

[vikas]

I am strill getting the same error.


Range(Cells(1, 8), Cells(NbListClients + 1, 12)) = ListClients

Feuil2Tri.Rows("1:3999").Insert Shift:=xlDown ----- this line error comes

'on place les en têtes de tri et la liste
For t = 1 To 13
Feuil2Tri.Cells(1, t) = "X" + Format(t)
Next t

Call FiltreGest(0, "", 0)
Call FiltreCliByGest(ListeGest, NbGest)

'on calcul le nbre de ligne de critère, et le nombre de ligne du tab final
NbligneCritere = Feuil2Tri.Range("a1").CurrentRegion.Rows.Count
NbLigneTabFinal = Feuil2Tri.Cells(NbligneCritere + 1,
20).CurrentRegion.Rows.Count


'on replace le tableau filtré a sa place
Feuil2Tri.Columns("A:S").Delete Shift:=xlToLeft
Feuil2Tri.Range(Rows(1), Rows(NbligneCritere)).Delete Shift:=xlUp
'Mantis 5239: BPS: Issue on the weekly when trying to change the period :
sjain
'Feuil2Tri.Rows("1:999").Insert Shift:=xlDown

Feuil2Tri.Rows("1:3999").Insert Shift:=xlDown

 

[Dave Peterson]

I don't have any other guesses.

I am strill getting the same error.

Range(Cells(1, 8), Cells(NbListClients + 1, 12)) = ListClients

Feuil2Tri.Rows("1:3999").Insert Shift:=xlDown ----- this line error comes

'on place les en têtes de tri et la liste
For t = 1 To 13
Feuil2Tri.Cells(1, t) = "X" + Format(t)
Next t

Call FiltreGest(0, "", 0)
Call FiltreCliByGest(ListeGest, NbGest)

'on calcul le nbre de ligne de critère, et le nombre de ligne du tab final
NbligneCritere = Feuil2Tri.Range("a1").CurrentRegion.Rows.Count
NbLigneTabFinal = Feuil2Tri.Cells(NbligneCritere + 1,
20).CurrentRegion.Rows.Count

'on replace le tableau filtré a sa place
Feuil2Tri.Columns("A:S").Delete Shift:=xlToLeft
Feuil2Tri.Range(Rows(1), Rows(NbligneCritere)).Delete Shift:=xlUp
'Mantis 5239: BPS: Issue on the weekly when trying to change the period :
sjain
'Feuil2Tri.Rows("1:999").Insert Shift:=xlDown

Feuil2Tri.Rows("1:3999").Insert Shift:=xlDown