Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

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

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

View Answers     

Similar Excel Tutorials

Top Excel Keyboard Shortcuts to Increase Productivity
I'll show you the top keyboard shortcuts for Excel that are sure to increase your productivity. These shortcuts are ...
Delete All Empty Rows or Blank Cells from a Range in Excel
How to quickly delete all empty cells or rows from a range in Excel.  This allows you to quickly clean your data to ...
Calculate the Total Time Worked Minus Lunch Breaks in Excel
Ill show you how to use Excel to calculate the total time worked in a day minus lunch time or any breaks. This is ...
Count the Number of Unique Values in a List in Excel
Use a formula to count the number of unique values that are contained within a list in Excel. Steps to Count the Nu ...
Stop Formula Calculation in Excel - Increase Worksheet Performance
I'll show you how to stop formulas and functions from performing their calculations and updating in Excel. This ma ...
How to Input, Edit, and manage Formulas and Functions in Excel
In this tutorial I am going to introduce how to input, edit and manage excel formulas. To start entering a formula, ...

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
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

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?


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



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.

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 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


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?


I want to insert a few columns in my workbook but I'm continually getting the error:

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

There is nothing from AD onwards, nor anything from row 111 down. I've deleted the cell contents and even the entire rows and columns to no avail....excel still thinks there's something in those cells.

Any thoughts?

I am trying to run a simple macro that will insert x number of rows depending upon the value in a cell. Here is the code:

Sub InsertRows()

Dim c As Range
Dim RowCount As Long

For Each c In ActiveSheet.Range("A2", Range("A" & Rows.Count).End(xlUp))
RowCount = c.Offset(0, 8)
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlToRight)).Select

Next c

End Sub
When it gets to the line "Rows.Insert.Resize(RowCount).xlShiftDown", I receive a Run-Time error 1004: Possible loss of data because Excel can't move non-blank cells of the worksheet. Find the last non-blank cell and delete everything in between that and your data set. Select cell A1 and save your worksheet to reset the last cell.

I have done this a few times, and have even closed out of Excel completely after clearing out the cells around my data set. I've tried copying the data into a new sheet, new workbook, etc. I can't see anything wrong with the code. I am able to successfully insert rows manually, just not through VBA. There are only 236 rows of data in my sheet....also, the RowCount value maxes out at 9.


Hey Everyone,

I have a procedure that creates a copies of a worksheet (template), renames and inserts them at a particular point in the workbook and then copies & pastes corresponding data from a database worksheet into this respective worksheets. The macro does this one at a time.

It works fine for the first 30-35 copies I use it, but if it goes above that number the macro fails and I get the following error:

Run-time error '1004'
Copy method of Worksheet class failed

After researching the issue, it appears to be a MS VBA bug where If you copy a sheet multiple times without closing the workbook periodically, you will get that error (source: hyperlink below).

Copying worksheet programmatically causes run-time error 1004 in Excel

I read MS' statement that, to get around this problem and it seems like there are only two options: (1) edit the code so that the workbook closes/reopens periodically while copying, or (2) make a template workbook instead of copying the sheet.

The first option would slow my macro down considerably, since the file takes a while to save and well I'm not really sure I understand the second option.

Is there an easier way to fix this issue or a better workaround then the two provided by Microsoft?

Any advice or help would be greatly appreciated!

Thank you in advance,


Hey all,

I'm having an issue with my workbook. I manage a shared database that my office uses in our everyday operations. I recently upgraded a macro that

-Deletes conditional formatting for a range.
-Formats the range with real formatting (based on cell values).
-Copies and Pastes range into an e-mail.

This macro runs fine, except for when the file is shared. Sharing is necessary for our daily operations so it's not something I can work around.

When I try to execute the macro, while shared i receive


Run-time error '1004': Application-defined or object-defined error.

It will not let me debug this error because you cannot open VBA while shared. If I unshare the file, I do not receive the error.

Does anyone know what could be causing this? I know this is a very vague question, but I'm stuck here and as far as I can see... everything should work fine.

Attached is the workbook so you can see what the script is. The macro in question is "GSEmail".

Many thanks in advance!

I have about 20 rows of data and I'm trying to insert a row of data before line 6. I highlight cell 6, click Insert /Rows and then I get an error, "To prevent the possible loss of data, Microsoft Excel cannot shift nonblank cells off the work sheet."
It goes on with some more garbage, but it's already obvious that Microsoft doesn't know what it's talking about. How do I get this insert to work?

Need some help to figure out why I am getting this error message.
"run time error 1004,
unable to set the numberformat property of the range class"

I have recorded a macro to run a time duration of a game.
Cell A1 Start time 15:35
Cell A2 End time (click button to run the macro, see below)
Cell A3 Time difference fromula (=A2-A1) This cell is formated to [mm]

Everything works great, when the sheet is not protected.
When I protect the sheet, I get the error message.

Without the Macro and the button and with the sheet protected, I use the Ctrl+Shift+: keys to record the End time. It works fine.]

Sub GameDuration()

ActiveCell.FormulaR1C1 = Now()
Selection.NumberFormat = "h:mm"
ActiveCell.Offset(2, 0).Select
End Sub

Any help in solving the above would be very much appreciated.


I need to use VBA to build a SumProduct formula and enter it into various cells in my worksheet. This is what I have done so far:


    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(('Data This Week'!$B$5:$B$992=B6)*('Data This Week'!$D$5:$D$992=D6)*('Data This Week'!$L$5:$L$992))-(SUMPRODUCT(('Data Last Week'!$B$5:$B$992=B6)*('Data Last Week'!$D$5:$D$992=D6)*('Data Last Week'!$O$5:$O$992)))"

When I try to run the procedure, I get Run-time error 1004 "Application-defined or object-defined error". The line starting with "ActiveCell" gets highlighted by the debugger.

The formula works fine when typed directly into the cell. So I have tried to use the macro recorder to record myself entering the formula, but then I get a message saying "Cannot be recorded."

Why is this error occuring and what would I need to do to fix it?

Hello, if anyone has experience with Run-time error 1004 or LockXLS, maybe you could give me some guidance.

I have a fully-working spreadsheet which uses macros. One of the macros codes has a line to save the activeworkbook.
It works fine as an xlsm file.

However, when I use LockXLS protection software to protect the file, it turns it into an exe file.
This exe file always produces a Run-time error 1004: Document Not Saved
whenever I run a certain macro.
It produces the error right when the code tries to save the workbook.

Is this a common problem with LockXLS? Is there any solution?

Thanks for your help!

I have a simple sheet with cells A6 to N6 defined as headers. These cells are uniformly formated as text with cell borders. All cells below this are for data entry. When I select the first data entry cell (A7) and use the standard Excel menu options Data/Forms, the form appears and works fine. When I try to create a Macro using the same sequence, the macro is created as:


This does not work and generates the following error:

Run-Time error '1004':
ShowDataForm method of worksheet class failed

Can this method be used in a Macro

I have an Excel .xlsm file which references external excel files (external app outputs to 2003 format). To consolidate the process across all of the individual output files, I have a VBA script based on

ActiveWorkbook.UpdateLink Name:="filename", Type:=xlExcelLinks

Though when I run the macro, save and exit. When I re-enter the file, Excel tells me the file is corrupt with unreadable content (pressing Ctrl, Shift + I returns the error code - 101648). It then runs off a laundry list of erroneous xml files, which is strange because i haven't a clue about XML.

However, when I manually update the references (enter the cell, press F2), save and exit - reentering the file, everything is fine? While i'm sure I can find an easy VBA alternative, i'd really like to get to the bottom of the issue, so I can navigate around in future projects.

I have a macro that runs fine using Excel 2007 on Windows XP, but with Excel 2010 on Windows 7 I get a 'Runtime error 1004' from the following line of code:

ActiveWorkbook.SaveAs Filename:="Teams-" & Range("A2").Value, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Am I overlooking something simple?

I getting an error while running a macro and I am lost. What the macro does is to reorder some columns in a spreadsheet of data that I imported from a flat file. Then it runs a sort and a filter that puts some column heading with a pull down box so it can filter out data by department. I can do the macro ok by hand and it works fine. I can record the macro just fine. When I play it back it stops part way through the ordering of the columns and says: Run-time error 1004

The selection is not valid. There are several possibile reasons:

Copy and paste areas cannot overlap unless they're the same size and shape.

If you're using the Create command on the Name submenu of the Insert menu, the row or column containing the proposed name ...

I read the help but it didn't help.

Any ideas? Thanks for any help



I have no idea how to write VBA but I do know how to do a key stroke macro to print and save it in a personal worksheet that opens when I open excel 2003. I have recorded a macro that prints a workbook that is emailed to me, it works for all 5 workbooks which are all different in the number of tabs in each workbook. I recorded a separate macro for each workbook because each one has to have the "fit to page" done to the tab before it can be printed. Each macro works fine except for 1, when I try to run the macro it makes no difference weather I use the short cut key assigned or run it from the macro window (alt+F8), it get this:

Run-time error '1004'; Select method of Worksheet class failed

I really don't understand too much about macros, I no how to unhide the personal macro workbook where this macro is at. When I look at the book I see a blank sheet but the macros are available. When I get the error I get the choice to DEBUG if I do that it opens a new screen for me; I guess to step through it. I am guessing that someone needs me to copy and paste the code here so you can see it? Or can you already guess what the problem is?


I have the following loop:


For iyrcol = 2 To 11
    For iperrow = 27 To 31
        Sheet.Cells(iperrow, iyrcol) = Application.WorksheetFunction. _
            Percentile(MC.Range( _
            MC.Cells(1, 1), MC. _
            Cells(sims, 1)).Offset(0, Sheet.Cells(26, iyrcol)), _
            Sheet.Cells(iperrow, 1) / 100)
    iyrcol = iyrcol + 2  'add two to jump over the merged cells

The loop is supposed to run 4 times to pull data off of a large monte carlo output worksheet and populate the actual report page that will eventually be handed to clients. For some reason it runs fine 3 of the 4 times and then gets hung up with the following error.

Run time error '1004': Unable to get the Percentile property of the WorksheetFunction class

I don't get how it can run through the procedure 3 times without any issues and then suddenly have an issue with the 4th time???

Any help would be greatly appreciated!

So i have Code in a few different modules and inorder for the code to run properly i need to unlock Sheet3 of my workbook, and then lock it again after the code has run. The Password is stored in sheet3.Range("L2"). But when one module runs it works and when another runs I get:

Run Time Error '1004' "The Password you supplied is not correct. Verrify that the CAPSLOCK key is off and besure to use the correct capitialization."

on open my code calls


Sub EnterFormulas() 
    Range("B7").Formula = "=IF(ISERROR(AVERAGE(A!K6,B!K6,C!K6,D!K6,E!K6,F!K6,G!K6,H!K6,I!K6,J!K6,K!K6,L!K6,M!K6,N!K6)),"",(AVERAGE(A!K6,B!K6,C!K6,D!K6,E!K6,F!K6,G!K6,H!K6,I!K6,J!K6,K!K6,L!K6,M!K6,N!K6)))" 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

Sorry it's a bit messy, but I edited the names of the individual worksheets as they are confidential.

My issue is that I have a workbook that has several sheets, all containing similar data provided by various parties. I have a final composite sheet that takes an average of the individual contributions for comparison purposes. I wrote the excel formulas into the actual cells initially, but now I want to make it so that a macro writes them in each time for me. There are actually more than one cell with formulas in but they are all the same as this one, just pointed at slightly different cells.

The error I receive is 'Run-time error '1004': Application-defined or object-defined error'. I have used this format ( -> range("xx").Formula = "xxxxx") many times before without issue and I do not understand what is causing this.

Is it possible that it thinks my formula is wrong? This code works if I change the formula (everything to the right of the '=' sign) to something basic, rather than nested etc. However, the formula is copied directly from the cell on the sheet, where it has been sat working perfectly for some time...

I have tried to find help on this but could not find anything directly related - any help appreciated.


P.S. using excel 2007/windows 7, although sheet was originally made on 2003/vista. however, don't think this will be an issue as everything else works fine...


I'm using the following code to "reset" a worksheet, that is, clear all contents, column/row grouping etc. For some worksheets there may not be any grouping, and when I run the macro I receive a Run-time error '1004' Ungroup method of Range class failed.

Can someone suggest an appropriate "On Error Resume" code for the following code, such that if the worksheet contains data the data is cleared and columns/rows reset?


' Unhide all Rows and Columns, Ungroup all Rows and Columns, Clear all cell contents _
    ' and reset Row height and Column wdith to 15
    With ActiveSheet.UsedRange
        .Rows.EntireRow.Hidden = False
        .Columns.EntireColumn.Hidden = False
        .ColumnWidth = 15
        .RowHeight = 15
    End With

When I run the maco in this file (see attached), I get the error:

Run-time error '1004':
Unable to get the VLookup property of the WorksheetFunction class

The debugger points to a line of code that refers to the first tab, but that's as much as I can understand.

I have been happily running the macro for months now on a daily basis and even yesterday it worked fine. Today, this error. Nothing has changed in the raw data as far as I can tell, but something is amiss.

I have attached the report, with the macro enabled, so it can be run and the error duplicated.

Any help is appreciated.

I am using Excel 2007. I created a macro today to hide all the rows in a spreadsheet that contain a zero value in them. However, the first time I run the macro I get this run time error. If I click "end" and then push the macro button again, it runs fine - no debugging necessary. Any ideas how I get the error message to stop popping up? My macro is as follows (and yes, it is filtering through over 5000 rows):

Sub AutoHide()
' AutoHide Macro
' Automatically hide rows with a 0 value.
' Keyboard Shortcut: Ctrl+h
ActiveSheet.Range("$A$3:$J$5071").AutoFilter Field:=10, Criteria1:=">0", _
End Sub

The section in red is what it is hilighting telling me is what is wrong when I click on debug. Again, I don't have to make any changes to anything, just go back to the button and click it again, and it runs fine. Does it matter that I made my spreadsheet a template? It seems like that is when I started getting this error, but I could be wrong.

Thank you!

When the file name doesn't exist then I get a run time error 1004. Which I know means that the file doesn't exist. However, I want to show/create a userform that tells other people using this worksheet the reason why it didn't work so that they can correct it themselves instead of asking me every time.

This is the code that looks for the file name in I2, in the folder Reports


ChDir "M:\Reports" 'Where to start looking in
    Workbooks.Open Filename:="M:\Reports\" & ActiveSheet.Range("I2") ' Opens the Billing file, the file name is in I2

What I would like to do is add the following:
IF the file does not exist (creates the run time error 1004) then
Exit Sub

Would this go in front or before my current piece of code?