“Selection.Insert Shift:=xlDown” gets Run-time error '1004'

**watsonea4** · 11-14-2012, 03:57 PM

First, I apologize, but I'm not versed in any kind of programming but I have the unfortunate job of supporting things with programming

The spreadsheet in question was created by someone who is no longer available to support it so any help you can give would be greatly appreciated.

When the macro is used, it gives this error:

"Microsoft Visual Basic

Run-time error '1004':

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 cells Excel considers nonblank. To do this, press CTRL+End to locate the last nonblank cell on the" (the rest is cut off)

It's supposed to take the row where you enter data and insert it in the table area below it after inserting a new row in which to paste it.

When I click to debug, the bold row below is highlighted.

Please Login or Register  to view this content.

I know that "Selection.Offset(1, 0)" inserts a single new row and if I change "If Selection.Offset(x, 0)." to "If Selection.Offset(1, 0).", the macro works but doesn't insert the row in the right place and having NO programming knowledge other than what I decode and figure out myself, so I don't even know what this "For x = 0 To y" business means.

Moderator's Note: Welcome to the forum, you have to put code tags around your codes. Select the code the hit the "#" sign

**AlphaFrog** · 11-14-2012, 04:26 PM

The code "thinks" all the rows on your worksheet "Export" are used all the way down to the last available row. All the rows are probably not actually used, but that's apparently what has happened.

So when the code tries to insert a new row 2, the very last row at the bottom of the worksheet (row 65536) would have to get bumped off the sheet and that causes the error.

If you select the row below the last row with data (the first blank row) and all the rows down to the very last row on the sheet. Then Edit\Clear\All. That should fix the problem.

**watsonea4** · 11-14-2012, 05:08 PM

It looks like all the rows ARE full (it's been in use for YEARS and they never clear any data out. I'll make them clear out the old stuff.

Thank you!

**AlphaFrog** · 11-14-2012, 05:18 PM

Originally Posted by watsonea4

It looks like all the rows ARE full (it's been in use for YEARS and they never clear any data out. I'll make them clear out the old stuff.

Thank you!

Alternatively, you could have the code clear the sheet's bottom row before inserting a new row. Be aware that you lose the bottom row of data.

Please Login or Register  to view this content.

It would be more efficient for them to archive and clear the "old" data though.

**goncalo_2697** · 01-02-2023, 07:02 AM

Hi, I'm trying to run the following code to add one empty line, but it only works if I run the macro as soon as I open the excel:

Sub LinhasAuto()
Dim lRow As Long
Dim RepeatFactor As Variant

lRow = 11
Do While (Cells(lRow, "A") <> "")

RepeatFactor = Cells(lRow, "AO")
If ((RepeatFactor > 0) And IsNumeric(RepeatFactor)) Then

Range(Cells(lRow, "A"), Cells(lRow, "AT")).Copy
Range(Cells(lRow + 1, "A"), Cells(lRow + RepeatFactor, "AT")).Select
Selection.Insert Shift:=xlDown

lRow = lRow + RepeatFactor
End If

lRow = lRow + 1
Loop
End Sub

If i change anything in the file after open it up and before run the code, I get the following error:

Attachment 811563

Thanks for your help!

**Glenn Kennedy** · 01-02-2023, 07:07 AM

This thread is 10 years old. Despite that, it's a forum rule that you start your own thread, rather than "piggy-backing" on someone else's thread. This helps prevent massive confusion arising - which will happen if it is not clear which question is being answered. So please start your own thread and explain your own problem (make sure that, right from the start, you use a meaningful title (NOT things like "help needed" or "urgent problem"... think of the Google search terms that you would use to find the solution).

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures. So, prepare a SMALL sample sheet (10-20 rows, not thousands!!!). Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

The yellow banner about sample worksheets, at the top of the screen tells you how to post a sheet here on the Forum.

Also read forum rule 2.

https://www.excelforum.com/forum-rul...rum-rules.html