Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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



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

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?


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.


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


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,


Hello,
I am importing external data into Excel 2003. The file is over 52,000 rows and has blank cells here and there as well as header and footer text in a number of rows. I am trying to clean up this data and delete the entire row if there is a blank cell anywhere in the row. I can use Excel's GOTO function.
Edit
Go To...
Special
Blanks

This highlights all the blank cells in the data range. Then I can use:
Edit
Delete
Entire Row
To remove all rows with any blank cells. This works wonderfully. However, if I record these actions as a macro and run it, I get the following error:
"Run-Time error '1004'
Cannot use that command on overlapping selections."

If I view the code for debugging (and I am getting a bit beyond my skills here) I see the following:
Code:

Sub clean_data()
'
' clean_data Macro
' Macro recorded 8/20/2007 by ME
'

'
    Range("A1").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireRow.Delete
End Sub


with the "Selection.EntireRow.Delete" row highlighted.

How come the functions work just fine sequentially but not in a macro? If I can save this action as a macro I can distribute it to other workers who will need to do the clean up on many subsequent files. If you have a suggestion I would appreciate it.
Thanks


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?


Hi,
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)
c.Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlToRight)).Select
Rows.Insert.Resize(RowCount).xlShiftDown

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.

Help...!


Hi I am trying to build a macro which will format the columns of a spreadsheet - basically it inserts some columns, writes formulas and highlights them. Here is a code I have got so far...

When I try to run this I get a run time error 1004 - Method 'Range' of 'Object'_Global' failed. The part of the code
Range("N2:N").FormulaR1C1 = "=(RC[-7]/RC[-2])"

is highlighted in the debugger.

Can anyone tell me why this is happening, also it would be great if you could suggest better ways of writing this code - as I am new to vba programming and most of my macros are built using the recorder and then 'working' on them.

Thanks.


Code:

Sub formatcolumns()

    Columns("G:G").Insert Shift:=xlToRight
    Range("H1").Select
    Selection.Copy
    Range("G1").Select
    ActiveSheet.Paste
    Columns("N:N").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight
    Range("N1").FormulaR1C1 = "=(RC[-1])"
    Range("N2:N").FormulaR1C1 = "=(RC[-7]/RC[-2])"
    Columns("N:N").Interior.ColorIndex = 36
    Columns("R:R").Insert Shift:=xlToRight
    Range("R1").FormulaR1C1 = "=(RC[-1])"
    Range("R2:R").FormulaR1C1 = "=(RC[-4]/RC[-2])"
    Columns("R:R").Interior.ColorIndex = 36
    Columns("U:U").Insert Shift:=xlToRight
    Range("U1").FormulaR1C1 = "=(RC[-1])"
    Range("U2:U").FormulaR1C1 = "=(RC[-14]*RC[-2])"
    Columns("U:U").Interior.ColorIndex = 36
    Columns("AC:AC").Insert Shift:=xlToRight
    Range("AC1").FormulaR1C1 = "=(RC[-1])"
    Range("AC2:AC").FormulaR1C1 = "=(RC[-5]+RC[-4]+RC[5]+RC[-22])/(RC[1]+RC[2])"
    Columns("AC:AC").Interior.ColorIndex = 15
    Columns("AJ:AJ").Interior.ColorIndex = 35
    Columns("AN:AN").Insert Shift:=xlToRight
    Range("AN1").FormulaR1C1 = "=(RC[-1])"
    Range("AN2:AN").FormulaR1C1 = "=(RC[-4]-RC[-33])"
    Columns("AN:AN").Interior.ColorIndex = 35
    Columns("AR:AR").Insert Shift:=xlToRight
    Range("AR1").FormulaR1C1 = "=(RC[-1])"
    Range("AR2:AR").FormulaR1C1 = "=(RC[-4]/RC[-3])"
    Columns("AR:AR").Interior.ColorIndex = 35
    Columns("BB:BD").Select
    Selection.Cut
    Columns("L:L").Insert Shift:=xlToRight
    Columns("M:N").Select
    Selection.Cut
    Columns("R:R").Insert Shift:=xlToRight
    Columns("J:J").Select
    Selection.Cut
    Columns("D:D").Insert Shift:=xlToRight
    Range("N2").Select
    Selection.AutoFilter
    ActiveWindow.FreezePanes = True
    Columns("AG:AG").Select
    Selection.Interior.ColorIndex = 34
    Columns("AH:AH").Select
    Selection.Interior.ColorIndex = 33
End Sub