Email:      Pass:    Pass?
Advertisements


Free Excel Forum

Run-time Error 1004: Cannot Shift Nonblank Cells Off The Worksheet

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have a macro running that creates a row, runs calculations, makes
references (values, not links), then deletes the row.

It works fine for several runs, but then I get this 1004 error.

"Run-time error '1004':

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

Try to locate the last nonblank cell by pressing CTRL+END, and delete
or clear all in cells between the last cell and the end of your data.
Then select cell A1 and save your workbook to reset the last cell
used."



If I save the file and exit out, it works fine for a few more runs. Its
a macro that will only be ran 1-2x per week, but still I want to make
sure this wont pose an issue several months down the road when someone
is performing data entry. I won't post the entire code, because it was
recorded, and incredibly long..but if you feel that would help I can
post it up.

FWIW, this is the actual piece of code giving the error:
Selection.Insert Shift:=xlToRight




Similar Excel Video Tutorials

Helpful Excel Macros

Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
Delete All Chart Sheets in Excel - Only Chart Sheets are Deleted - Not Embedded Charts
- Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are in the
Change Formulas to Absolute or Relative References
- This macro will change the formulas in cells to absolute or relative. It allows you to change the rows or the columns o
Automatically Run a Macro at a Certain Time - i.e. Run a Macro at 4:30PM every day
- Automatically run an Excel macro at a certain time. This allows you to not have to worry about running a macro every da
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth

Similar Topics







i have a macro that deletes all rows from row 4 to 65536.

i then paste results from a query in row 4, and then run a macro that loops through each column, adds a column, and adds a ranking formula. sometimes, but not all of the time, i get an error message saying:

Run-time error '1004'

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

Try to locate the last nonblank cell by pressing CTRL+END, and delete or clear all in cells between the last cell and the end of your data. Then select cell A1 and save your workbook to reset the last cell used.

When i hit CTRL+END, I arrive at a cell, but it is blank. If I delete everything, the problem still occurs. Anyway around this?


Hello,

I'm facing a problem in Inserting row using the command:

Code:

Rows("2").EntireRow.Insert


Find the below detailed error message:

Run-time error '1004'

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

Try to locate the last nonblank cell by pressing CTRL+END, and delete or clear all in cells between the last cell
and the end of your data.
Then select cell A1 and save your workbook to reset the last cell used.

Can you please help me on this.


I am working on an excel worksheet which is very large. When I try to add a new column in the worksheet, I get an error message that says
"To prevent possible loss of data, microsoft excel cannot shift nonblank cells off the worksheet.
Try to locate the last nonblank cell by pressing ctrl+end, and delete or clear all in cells between the last cell and the end of your data . then select a1 and save your workbook to reset the last cell used. or you can move the data to a new location and try again."

What can i do that will allow me to get through this error and add a column to this existing spreadsheet.??? Thanks


Hi,

I was trying to insert a new column in the spreadsheet but received the following error message:

To prevent possible loss of data, Microsoft Office Excel cannot shift nonblank cells off the worksheet.
Try to locate the last nonblank cell by pressing CTRL + END, and delete or clear all in cells between the last cells and the end of your data. Then select cell A1 and save your workbook to reset the last cell used.
Or, you can move the data to a new location and try again.

What i did was I selected all the columns from A to E (because these are the columns that I have my data in). Yes, there are some blank cells in there.
Then, I went to Edit in the menu toolbar, click Clear -> All but received another error message saying "Cannot change part of merged cell".

I wonder if anyone might be able to help me workaround this so that I can insert a new column again.

I am using Excel 03.

Thank you in advance




What is this error message that is popping up when I try to insert a blank row of cells?

"To prevent possible loss of data, Excel cannot shift nonblank cells off of the worksheet. Select another location in which to insert new cells, or delete data from the end of your worksheet.

If you do not have data in cells that can be shifted off the worksheet, you can reset which cells Excel considers nonblank. To do this, press CTRL + End to locate the last nonblank cell on the worksheet. Delete this cell and all cells between it and the last row and column of your data then save."

This excel document we have has LARGE amounts of data and we can't afford to lose any of it.

-Brit

I have a file that someone has evidently selected all and set the background to white. When I try to add a column to the data, I get an error that says the below. There doesn't appear to be any data in the cells, just the white pattern formatting. Any thoughts on how to be able to insert columns without selecting the columns and using Clear > All to make columns available to move again?

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

Try to locate the last nonblank cell by pressing CTRL+END, and delete or clear all in cells between the last cell and the end of your data. Then select Cell A1 and save your workbook to reset the last cell used.

Or, you can move the data to a new location and try again.


Hi Folks,
can some one please help me with the following error message coming when try to insert the column or row to the spreadsheet with a yellow exclamation mark?
The error is:
"To prevent possible loss of data, Excel cannot shift nonblank cells of the worksheet. Select another location in which to insert new cells, or delete data from the end of your worksheet.

If you do not have data in cells that can be shifted off of the worksheet, you can resent which cells Excel considers nonblank, To do this, press CTRL+END to locate the last nonblank cell on the worksheet. Delete this cell and all the cells b/w it and the last rwo and column of your data than save."

Any help as what on earth I can get around this problem?
thx


hi, my worksheet seems to have limited column range. Last column is R and I can't insert any more column. I cant also move my cursor to the right of R. Im getting the error:

"To prevent possible loss of data, Excel cannot shift nonblank cells off of the worksheet. Select another location in which to insert new cells, or delete data from the end of your worksheet.
If you do not have data in cells that can be shifted off of the worksheet, you can reset which Excel considers nonblank. To do this, press CTRL+End to locate the last nonblank cell on the worksheet. Delete this cell and all cells between it and the last row and column of your data then save."

I have tried the instruction above but still last column is R and cant insert more. Last nonblank cell is already in column R. There's also no hidden columns either. I've selected all and unhide but still, last column is R.

Help pls on how to expand the range and add column. Thanks!




I have a worksheet, which has a multitude of data in the form of text, numbers, and some simple formulas

however, i want to add a column and every time i highlight a column, right click, and select insert, i get the following message:

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

Try to delete or clear the cells to the right and below the data. Then select cell A1 and save your workbook to reset the last cell used.

Or, you can move the data to a new location and try again.

pls help

I have inherited a spreadsheet that I did not create. I am trying to insert
columns, but when I attempt to do this, I get an error statement saying
"To prevent possible loss of data, Microsoft Excel cannot shift nonblank
cells of the worksheet. Try to delete or clear the cells to the right and
below your data. then select cell A1, and save your workbook to reset the
last cell used. Or, you can move your data to a new location and try again.
I have done everything I think I can do, including clearing out the entire
spreadsheet un-merging cells. I still get the same error statement. Can
anyone please help put me on the right track.

Thanks.



Hi,

I am using excel 2003 in my QTP automation framework as an input data sheet.When i tried to add columns in the sheet, i was not able to insert and i was getting a message "To Prevent possible loss of data,Microsoft Office Excel cannot shift nonblank cells off the worksheet.
Try to locate the last nonblank cell by pressing CTRL+END, and delete or clear all in cells between the last cell and the end of your data. Then select cell A1 and save your workbook to reset the last cell used.
Or, you can move the data to a new location and try again". Screenshot of the message is attached.



Please help me in this regard.
My second question, is there any limitation on number of rows and column in a spreadsheet?

Because before inserting columns, i saw there were two empty columns at the end of the sheet column wise, so i was able to add two columns, but after that, i was not able to insert columns.

Note: I am using the sheet, which has been used in my project, I mean this is an old sheet.


I have set up a module in Visual Basic which copies a summary of information, but puts the information into a different format on a new worksheet. (Some columns are shifted, some deleted, format changed etc).

When I try to run the macro I am getting the following run-time error:

'To prevent possible loss of data, Microsoft Excel cannot shift nonblank cells off the worksheet. Try to delete or clear the cells to the right and below your data. Then select cell A1, and save your workbook to reset the last cell used.'

This doesn't make sense to me, as I am tring to copy all non blank cells!

Is this a common error code, and if so, how should I go about rectifying this?

Many thanks.


A simple request...I want to insert a column in a worksheet...something I've done thousands of times...and now I get the following message:

"To prevent possible loss of date, Micro. Office Excel cannot shift nonblank cells off the worksheet.

Try to located the last non blank cell by pressing CTL + End, and delete or clear all in cells between the last cell and the end of your data.

Then select cell A1 and save your workbook to reset the last cell used.

Or, you can move the data to a new location and try again."

What is that all about?? I went to the bottom of my page to try again to insert and I get the same error message. I am clueless as to why that is happening. I've always inserted columns in the middle of my information and never had a problem before.

HELP !!


I need help.
I have this HUGE spreadsheet & I've run out of columns & can't add anymore.
I get this message:
To prevent possible loss of data, MS Excel cannot shirt nonblank cells off
the worksheet.
Try to locat the last nonblank cell by pressing ctrl&end & delete or clear
all in cells between the last cell and the end of your data. Then select cell
A1 and save your workbook to reset the last cell used.
or you can move the data to a new location & try again.
My last cell is IV 222. Is IV as far as it goes???
If so, how can I make a TOTALS columnn on a new sheet but use figures from
this huge sheets???

Thanks for ANY & all help

Trish





I'm trying to insert columns into a spread sheet. It lets me insert 2.
From then on it won't let me insert any more.
It says: "to prevent loss of data, Microsoft Excel cannot shift
nonblank cells off the worksheet. Try to delete or clear the cells to
the right and below your data. then select A1, and save your workbook
to reset the last cell used. Or you can move the data to a new
location and try again." I've tried those things and they don't work.


What does?


--
TROUBLE



I am trying to insert a columns between different columns, but every time I
try I get a message stating that MS Excel can't shift nonblank cells off the
worksheet. I am not trying this, I only have 12 columns, so there should be
plenty of room for more colums. It goes on to say to try to locate the last
nonblank cell by pressing CTRL+END, and delete or clear all in cells between
the last cell and the end of your data, which I have done, but it still will
not allow me to add columns where needed. Any suggestions would be greatly
appreciated.

Thanks in advance
--
Pam



I have recorded a macro to insert multiple columns throughout a spreadsheet and I got the error: Compile Error: Wrong number of agruments or invalid property assignment.

I deleted all the code for my selecting cells as I scrolled to the right as I was adding columns and it is down to only selecting the applicable columns and inserting the number of columns in to the right of the selected column. I do not see where there is an error in the code. I do not get a highlight or underlining of text to indicate the error.

Thanks for your help.
Rodney Jorgensen

Sub Columns()
'
' Columns Macro
' Inserting Blank Columns
'

'
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("N:N").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("R:R").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("V:V").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AB:AB").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AF:AF").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AJ:AJ").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AO:AO").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AY:AY").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BC:BC").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BG:BG").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BJ:BJ").Select
Selection.Insert Shift:=xlToRight
Columns("BL:BL").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BP:BP").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BX:BX").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("CD:CD").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("CI:CI").Select
Selection.Insert Shift:=xlToRight
Columns("CN:CN").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("CT:CT").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("DF:DF").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("EV:EV").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("EZ:EZ").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FD:FD").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FH:FH").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FL:FL").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FP:FP").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FW:FW").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("GK:GK").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("GO:GO").Select
Selection.Insert Shift:=xlToRight
Columns("GS:GS").Select
Selection.Insert Shift:=xlToRight
Columns("GV:GV").Select
Selection.Insert Shift:=xlToRight
Columns("GY:GY").Select
Selection.Insert Shift:=xlToRight
Columns("HB:HB").Select
Selection.Insert Shift:=xlToRight
Columns("HF:HF").Select
Selection.Insert Shift:=xlToRight
Columns("HI:HI").Select
Selection.Insert Shift:=xlToRight
Columns("HL:HL").Select
Selection.Insert Shift:=xlToRight
Columns("HO:HO").Select
Selection.Insert Shift:=xlToRight
Rows("1:1").Select
Range("HI1").Activate
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Rows("11:11").Select
End Sub


Hello All:

The below section of code has been running well all day but now I'm getting an error message: Run-time error '1004': To prevent possible loss of date, Microsoft Excel cannot shift nonblank cells off the worksheet. Can someone shed some insight?

Cells.Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4, 8, 9, 11 _
, 12, 14, 15), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Cells.Select
Selection.Copy
Sheets("Sheet2").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

The error occurs on the Selection.Subtotal -- line 2.


Am not an Excel expert - Using Excel 2000, have a spreadsheet to which we are
continually adding rows of information - Current entries are 10,304 and using
columns A-Q. Today, when we tried to insert blank rows to add information
(as always in the past) we received the following popup message:
"To prevent possible loss of data, Microsoft Excel cannot shift nonblank
cells off the worksheet.
Try to delete or clear the cells to the right and below your data. Then
select cell A1, and save your workbook to reset the last cell used."
Why are we suddenly receiving this message. We do not want to lose data.



Hi all,

I'm having a problem with Excel 2000 when I am trying to insert a new row in
the middle of an existing worksheet.

I keep getting an error message that says:
"To prevent possible loss of data, Microsoft Excel cannot shift nonblack
cells off the worksheet.

Try to delete or clear the cells to the right and below your data. Then
select A1, and save your workbook to reset the last cell used.

Or, you can move the data to a new location and try again."

The worksheet goes down to row 18398, and I have tried deleting and clearing
every cell below and to the right of this row to make sure that there is no
data hidden away anywhere.

However it doesn't seem to make any difference, I still cannot enter a new
row. It did allow me to actually insert a row, but then the next time I tried
insert a row I got the same error message.

Any help will be appreciated
Thanks in advance



I've written a small macro and everything was going fine until I tried to input a formula into a given cell, when the macro runs to this point I get a "Run-time error '1004': Application-defined or object-defined error" and when I check the line I've found that it gives me a "error 2015" which appears to be date related according to microsoft but that makes little sense to me.
Code:

    Dim A, B, C As Object
    Dim RA, RB, RC, RD As Range
    
    Worksheets("FM_ValExport").Select
    FR = Range("A1000").End(xlUp).Row
    While Range("B" & CStr(FR)) <> "WESINTERUT"
        FR = FR - 1
    Wend
    SR = FR
    While Range("B" & CStr(SR)) <> "WESGROWTUT"
        SR = SR - 1
    Wend
    Range("A" & CStr(SR) & ":Q" & CStr(FR - 4)).Copy
    Sheets.Add
    ActiveSheet.Name = "Growth"
    Cells(1, 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Range("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Range("F:N").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:F").Cut
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    RA = Range("A" & SR & ":Q" & FR).Address
    Columns("G:G").Select
    Selection.NumberFormat = "0.00"
    Range("G5").Select
    ActiveCell.Offset(0, 0) = "=VLOOKUP(RC[-2],FM_ValExport!" & RA & ",5,FALSE)"


I get the error on the last line of my code but strangly it doesn't happen if I cut it down to just "=(FM_ValExport!" & RA & ")".
Any suggestions are appreciated.


I have created the following VBA using a macro function in Excel which runs fine. When copying it across to a button in the application I get the following error. Any ideas?

Sheets("foodhistory").Select
Rows("1:46").Select
Selection.insert Shift:=xlDown

Run time error 1004. Select method of Range class failed


Hi
I use excel from Office Xp
I am treasurer but with no previous excel experience (self taught!) I inherited a spreadsheet about 6 years ago for our church accounts that was set up by an accountant!! -no longer contactable and until now it has been adequate for our needs.(although have tweaked it here and there)

Currently it has A to IV columns across top(divided into different funds) and 6551 rows down, info feeds into other sheets. I also have split screens if that makes any difference
I am now trying to add more columns (have added some before with no problems)but it comes up with the following message

'To prevent possible loss of data, Microsoft Excel cannot shift non-blank cells off the worksheet Try to locate the last non-blank cell by pressing CTRL+END, and delete or clear all in cells between the last cell and the data. Then select A1 and save your workbook to reset last cell used'

Have pressed ctrl+end and I go to IO 917
Last column that has data in is IN and last row I can see data in is 510 nothing to clear

?? stuck I don't want to shift anything so do I need to make the worksheet bigger so I can add some more columns.
Many thanks


I have a loop that works for one sheet, and when i have about 40,000 lines it works fine. But then i have some other data, which is about 500,000 lines, & when i split it into 20,000 or however it causes an error in the loop. The loop runs, but then eventually says theres too much data & afterwards it wont save either and the loop wont run.

Code:

' this works fine if i have 30,000 rows or 45000
Sub adcls()
Dim Rng As Range
Dim iCell As Range


Set Rng = Range("b11:b" & Cells(Rows.Count, 2).End(xlUp).Row)
    For Each iCell In Rng
          If iCell.Offset(0, 2) = "1324I" Then
        iCell.Resize(1, 4).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    End If
Next iCell



End Sub

'this one causes errors, even if i cut data to 3000 lines for sheet #2 it eventually says 

'too much data

Sub nowrk()
Dim Rng As Range
Dim iCell As Range


Set Rng = Range("b3:b" & Cells(Rows.Count, 5).End(xlUp).Row)
    For Each iCell In Rng
          If iCell.Offset(0, 1) = "CC" Then
        iCell.Resize(1, 9).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ElseIf iCell.Offset(0, 1) = "2dt" Then
    iCell.Resize(1, 26).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ElseIf iCell.Offset(0, 1) = "Dates" Then
    iCell.Resize(1, 27).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   
    End If
Next iCell



End Sub


any help and guidence on this data error, is the data corrupted? Do I need to paste the data as special values of some time in order for the loop to continue to run. Sometimes it runs about 15,000 rows and then crashes, then i tried doing 4,000 rows but then crashes at 3,000 rows (the second program).

any help, appreciated,


When I run the following code, I get a Run-time Error '1004': Delete method of Range class failed. If there is nothing in column D, then the if statement is not satisfied and the macro runs fine and doesn't do anything.
Changing the Delete line to 'Rows(i).Delete Shift=x1Up' works just fine, but I would prefer to just delete the selected range.
Here is the entire macro:
Code:

Sub AssignRequests()
    Dim i As Integer
    i = 2
    Do While i <= Range("OutstandingCount") + 10
    If Range("D" & i) <> "" Then
    Range("PasteRange").Value = Range("A" & i, "D" & i).Value
    
       Range("A" & i, "D" & i).Delete Shift:=x1Up
    
    Else: i = i + 1
    End If
    Loop
End Sub


This is my first post, so I hope it is done correctly.
Thanks!