Adding more than 65,536 rows in Excel 2007
WL
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
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
"WL" <W...@discussions.microsoft.com> wrote in message
news:2B185F62-857D-478F...@microsoft.com...
vikas
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
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.
--
Dave Peterson
vikas
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" wrote:
> .
>
Dave Peterson
--
Dave Peterson