empty cell - inserting

[kenguru]

I got the following error:
Run time error 1004
Excel cannot shift nonblank cells of the worksheet.

I can't locate this one I don't see to what it refers

Any ideas?

Kenguru

 

[misterstick]

there is a non-blank cell in either the last row, or the last column of the workbook.

by inserting a cell you're moving all the cells to the right further right and those below further down.

the last cells in the row and column are removed.

for obvious reasons, only blank cells can be removed like this.

in excel itself, select the sheet and press control-end to find out what the last cell in the used range is. this may well be blank. if it is, go up (end then up) if you're in the last column, or left (end then left) if you're in the last row to find out which is the offending cell.

that's one big spreadsheet you've got there.

good luck.


mr s. <

 

[kenguru]

This is the code:
Sheets(aktiv_sheet).Select
mezo = ii + 1 & ":" & ii + 1
Rows(mezo).Select
Selection.Insert Shift:=xlDown

 

[Fenrirshowl]

One of the cells that would be moved off the worksheet either in the same row or column is not empty and cannot therefore be removed in this way.

Check the cell (depending on whether you are shifting cells right or down and if empty select and press delete to make doubly sure). Hopefully you should then be able to insert the cell.

Fen

 

[kenguru]

Maybe I didn't understand you weel, but i did the following think:
i pressed CTRL-END and the Excel "went down" to line: 66517 and the last column

 

[Fenrirshowl]

Kenguru

Looks like you want to insert a row, not a cell, in which case try

Rows(ii+1).Entirerow.Insert

Fen

 

[kenguru]

I wrote the following, but i got an error:
Sheets(aktiv_sheet).Select
Sheets(sheet).Rows(ii + 1, 1).EntireRow.Insert Shift:=xlDown



aktiv_sheet is a number, obviously the active sheet

 

[kenguru]

The error was (sorry for not writing it):
Application defined or object defined error

 

[Fenrirshowl]

If you are working on the activesheet, just copy the following:

Activesheet.Rows(ii+1).EntireRow.Insert

No selection or shifting down is required.

 

[kenguru]

I got the same error on the same line: Cannot shift cells

 

[Fenrirshowl]

Ok, it seems we are back to the original problem. One of the cells in row 65536 is not empty so the macro is stopping rather than allowing you to delete the data.

Can you confirm whether any of the cells on row 65536 should be non-empty?

If they should all be blank you can insert

Rows(65536).ClearContents

then

Rows(ii+1).EntireRow.Insert

to get around it. But check the entries in 65536 first.

 

[xlbo]

You have been told several times why this is happening and seem to have ignored it completely. You have what excel sees as a non blank cells in either your last column (IV) or last row (65536). There are a number of causes of this so:

have you applied formats to all cells in worksheet ?

If so, don't - clear formats except from cells you will actually use

Go to last column and delete it
Go to last row and delete it

try again


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Generally speaking, it is often unnecessary and counterproductive to use the INSERT method.

In many cases, it is better to find the bottom of a range and add data there and sort the new data into order.

It is also not a sign of good organization and best practices, that extraneous data prevents operations like INSERT. This is a symprom, I fear, of other, more significant problems.

Skip,

_[red][/red]