+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 16

Why can't I insert a row in my worksheet?

  1. #1
    susanholland3
    Guest

    Why can't I insert a row in my worksheet?

    I am trying to insert a row of simple data into my worksheet. When I do, I
    get the following message:

    "This operation is not allowed. The operation is attempting to shift cells
    in a list on your worksheet."

    I don't understand this. I have created lists from the worksheet but don't
    have any now. Why does it think I have a list on my worksheet? And, if I have
    one, how do I remove it?

    I couldn't find anything on this in the knowledge base and am very
    frustrated. Thanks for any help.

  2. #2
    Michael M
    Guest

    RE: Why can't I insert a row in my worksheet?

    Hi Susan
    It sounds like the location your are trying to insert a row has merged cells
    in it.
    You will need to either unmerge the cells or go 1 row above or below the
    merged area.

    HTH
    Michael

    "susanholland3" wrote:

    > I am trying to insert a row of simple data into my worksheet. When I do, I
    > get the following message:
    >
    > "This operation is not allowed. The operation is attempting to shift cells
    > in a list on your worksheet."
    >
    > I don't understand this. I have created lists from the worksheet but don't
    > have any now. Why does it think I have a list on my worksheet? And, if I have
    > one, how do I remove it?
    >
    > I couldn't find anything on this in the knowledge base and am very
    > frustrated. Thanks for any help.


  3. #3
    susanholland3
    Guest

    RE: Why can't I insert a row in my worksheet?

    I don't have any merged cells, at least not that I know of or that I
    intended. I have created lists from this worksheet and wonder if it thinks I
    still have a list open, but don't know how to correct for that.

    I tried locating the row above and below where I need it----it didn't work.
    Thanks for trying.

    "Michael M" wrote:

    > Hi Susan
    > It sounds like the location your are trying to insert a row has merged cells
    > in it.
    > You will need to either unmerge the cells or go 1 row above or below the
    > merged area.
    >
    > HTH
    > Michael
    >
    > "susanholland3" wrote:
    >
    > > I am trying to insert a row of simple data into my worksheet. When I do, I
    > > get the following message:
    > >
    > > "This operation is not allowed. The operation is attempting to shift cells
    > > in a list on your worksheet."
    > >
    > > I don't understand this. I have created lists from the worksheet but don't
    > > have any now. Why does it think I have a list on my worksheet? And, if I have
    > > one, how do I remove it?
    > >
    > > I couldn't find anything on this in the knowledge base and am very
    > > frustrated. Thanks for any help.


  4. #4
    Michael M
    Guest

    RE: Why can't I insert a row in my worksheet?

    OK
    Are the some of the toolbars greyed out ??
    If so, I would suggest that you're sheet is protected and needs to be
    unprotected.
    "The shift cells error " definately appears when there are merged cells

    HTH
    Michael M

    "susanholland3" wrote:

    > I don't have any merged cells, at least not that I know of or that I
    > intended. I have created lists from this worksheet and wonder if it thinks I
    > still have a list open, but don't know how to correct for that.
    >
    > I tried locating the row above and below where I need it----it didn't work.
    > Thanks for trying.
    >
    > "Michael M" wrote:
    >
    > > Hi Susan
    > > It sounds like the location your are trying to insert a row has merged cells
    > > in it.
    > > You will need to either unmerge the cells or go 1 row above or below the
    > > merged area.
    > >
    > > HTH
    > > Michael
    > >
    > > "susanholland3" wrote:
    > >
    > > > I am trying to insert a row of simple data into my worksheet. When I do, I
    > > > get the following message:
    > > >
    > > > "This operation is not allowed. The operation is attempting to shift cells
    > > > in a list on your worksheet."
    > > >
    > > > I don't understand this. I have created lists from the worksheet but don't
    > > > have any now. Why does it think I have a list on my worksheet? And, if I have
    > > > one, how do I remove it?
    > > >
    > > > I couldn't find anything on this in the knowledge base and am very
    > > > frustrated. Thanks for any help.


  5. #5
    Michael M
    Guest

    RE: Why can't I insert a row in my worksheet?

    Susan
    This can also apply to cells in columns as well

    MM

    "susanholland3" wrote:

    > I don't have any merged cells, at least not that I know of or that I
    > intended. I have created lists from this worksheet and wonder if it thinks I
    > still have a list open, but don't know how to correct for that.
    >
    > I tried locating the row above and below where I need it----it didn't work.
    > Thanks for trying.
    >
    > "Michael M" wrote:
    >
    > > Hi Susan
    > > It sounds like the location your are trying to insert a row has merged cells
    > > in it.
    > > You will need to either unmerge the cells or go 1 row above or below the
    > > merged area.
    > >
    > > HTH
    > > Michael
    > >
    > > "susanholland3" wrote:
    > >
    > > > I am trying to insert a row of simple data into my worksheet. When I do, I
    > > > get the following message:
    > > >
    > > > "This operation is not allowed. The operation is attempting to shift cells
    > > > in a list on your worksheet."
    > > >
    > > > I don't understand this. I have created lists from the worksheet but don't
    > > > have any now. Why does it think I have a list on my worksheet? And, if I have
    > > > one, how do I remove it?
    > > >
    > > > I couldn't find anything on this in the knowledge base and am very
    > > > frustrated. Thanks for any help.


  6. #6
    susanholland3
    Guest

    RE: Why can't I insert a row in my worksheet?

    Well, I hate to be difficult but it's still not working. None of the toolbars
    are grayed out. The sheet is not protected (I double-checked) and the cells
    are not merged (I double-checked on that, too).

    But here's some more information I'm gleaning as I work with this: I have
    created lists from the columns of this worksheet. On the column headings
    where I have previously created lists, I click on the heading and get the
    drop down list--as though the list is still there and part of the worksheet.
    I think that is why I can't add a row----a row cuts across those columns that
    think they still have lists.

    Does that make any sense? I think I need to somehow get rid of the lists or
    convince the worksheet that I don't care about that.

    Thanks for the efforts so far.....

    Susan
    "Michael M" wrote:

    > OK
    > Are the some of the toolbars greyed out ??
    > If so, I would suggest that you're sheet is protected and needs to be
    > unprotected.
    > "The shift cells error " definately appears when there are merged cells
    >
    > HTH
    > Michael M
    >
    > "susanholland3" wrote:
    >
    > > I don't have any merged cells, at least not that I know of or that I
    > > intended. I have created lists from this worksheet and wonder if it thinks I
    > > still have a list open, but don't know how to correct for that.
    > >
    > > I tried locating the row above and below where I need it----it didn't work.
    > > Thanks for trying.
    > >
    > > "Michael M" wrote:
    > >
    > > > Hi Susan
    > > > It sounds like the location your are trying to insert a row has merged cells
    > > > in it.
    > > > You will need to either unmerge the cells or go 1 row above or below the
    > > > merged area.
    > > >
    > > > HTH
    > > > Michael
    > > >
    > > > "susanholland3" wrote:
    > > >
    > > > > I am trying to insert a row of simple data into my worksheet. When I do, I
    > > > > get the following message:
    > > > >
    > > > > "This operation is not allowed. The operation is attempting to shift cells
    > > > > in a list on your worksheet."
    > > > >
    > > > > I don't understand this. I have created lists from the worksheet but don't
    > > > > have any now. Why does it think I have a list on my worksheet? And, if I have
    > > > > one, how do I remove it?
    > > > >
    > > > > I couldn't find anything on this in the knowledge base and am very
    > > > > frustrated. Thanks for any help.


  7. #7
    susanholland3
    Guest

    RE: Why can't I insert a row in my worksheet?

    Well, after all that.....I figured it out! I clicked on the columns that were
    lists and used the list drop down menu----clicked on "change to range" (I
    still don't know what that means). At any rate, the worksheet no longer has
    lists attached and I was able to add the row.

    Thanks for the help.

    "susanholland3" wrote:

    > I am trying to insert a row of simple data into my worksheet. When I do, I
    > get the following message:
    >
    > "This operation is not allowed. The operation is attempting to shift cells
    > in a list on your worksheet."
    >
    > I don't understand this. I have created lists from the worksheet but don't
    > have any now. Why does it think I have a list on my worksheet? And, if I have
    > one, how do I remove it?
    >
    > I couldn't find anything on this in the knowledge base and am very
    > frustrated. Thanks for any help.


  8. #8
    SteveW
    Guest

    Re: Why can't I insert a row in my worksheet?

    Are you able to delete a row ?

    Then it must be you lsits.

    I've recreated your problem by giving a cell data validation with a list=

    And using a whole column for that list

    Rows can be inserted, deleted - until you put a value in the last row !

    Check the list definitions - guess some of them refer to columns

    Now in normal case the empty cells of the column will be *empty*
    but maybe you filled the column with blanks or or or

    Steve


    On Thu, 03 Aug 2006 13:43:02 +0100, susanholland3 =

    <susanholland3@discussions.microsoft.com> wrote:

    > Well, I hate to be difficult but it's still not working. None of the =


    > toolbars
    > are grayed out. The sheet is not protected (I double-checked) and the =

    =

    > cells
    > are not merged (I double-checked on that, too).
    >
    > But here's some more information I'm gleaning as I work with this: I h=

    ave
    > created lists from the columns of this worksheet. On the column headin=

    gs
    > where I have previously created lists, I click on the heading and get =

    the
    > drop down list--as though the list is still there and part of the =


    > worksheet.
    > I think that is why I can't add a row----a row cuts across those colum=

    ns =

    > that
    > think they still have lists.
    >
    > Does that make any sense? I think I need to somehow get rid of the lis=

    ts =

    > or
    > convince the worksheet that I don't care about that.
    >
    > Thanks for the efforts so far.....
    >
    > Susan
    > "Michael M" wrote:
    >
    >> OK
    >> Are the some of the toolbars greyed out ??
    >> If so, I would suggest that you're sheet is protected and needs to be=


    >> unprotected.
    >> "The shift cells error " definately appears when there are merged cel=

    ls
    >>
    >> HTH
    >> Michael M
    >>
    >> "susanholland3" wrote:
    >>
    >> > I don't have any merged cells, at least not that I know of or that =

    I
    >> > intended. I have created lists from this worksheet and wonder if it=

    =

    >> thinks I
    >> > still have a list open, but don't know how to correct for that.
    >> >
    >> > I tried locating the row above and below where I need it----it didn=

    't =

    >> work.
    >> > Thanks for trying.
    >> >
    >> > "Michael M" wrote:
    >> >
    >> > > Hi Susan
    >> > > It sounds like the location your are trying to insert a row has =


    >> merged cells
    >> > > in it.
    >> > > You will need to either unmerge the cells or go 1 row above or =


    >> below the
    >> > > merged area.
    >> > >
    >> > > HTH
    >> > > Michael
    >> > >
    >> > > "susanholland3" wrote:
    >> > >
    >> > > > I am trying to insert a row of simple data into my worksheet. =


    >> When I do, I
    >> > > > get the following message:
    >> > > >
    >> > > > "This operation is not allowed. The operation is attempting to =

    =

    >> shift cells
    >> > > > in a list on your worksheet."
    >> > > >
    >> > > > I don't understand this. I have created lists from the workshee=

    t =

    >> but don't
    >> > > > have any now. Why does it think I have a list on my worksheet? =

    =

    >> And, if I have
    >> > > > one, how do I remove it?
    >> > > >
    >> > > > I couldn't find anything on this in the knowledge base and am v=

    ery
    >> > > > frustrated. Thanks for any help.




    -- =

    Steve (3)

  9. #9
    SteveW
    Guest

    Re: Why can't I insert a row in my worksheet?

    Good,
    I posted a reply before I read yours
    But I think you must have a later version of Excel than me (2000)
    as I don't get any of your List menus, etc etc.

    Anyway glad you got it sorted.
    Steve

    On Thu, 03 Aug 2006 14:00:02 +0100, susanholland3 =

    <susanholland3@discussions.microsoft.com> wrote:

    > Well, after all that.....I figured it out! I clicked on the columns th=

    at =

    > were
    > lists and used the list drop down menu----clicked on "change to range"=

    (I
    > still don't know what that means). At any rate, the worksheet no longe=

    r =

    > has
    > lists attached and I was able to add the row.
    >
    > Thanks for the help.
    >
    > "susanholland3" wrote:
    >
    >> I am trying to insert a row of simple data into my worksheet. When I =

    =

    >> do, I
    >> get the following message:
    >>
    >> "This operation is not allowed. The operation is attempting to shift =

    =

    >> cells
    >> in a list on your worksheet."
    >>
    >> I don't understand this. I have created lists from the worksheet but =

    =

    >> don't
    >> have any now. Why does it think I have a list on my worksheet? And, i=

    f =

    >> I have
    >> one, how do I remove it?
    >>
    >> I couldn't find anything on this in the knowledge base and am very
    >> frustrated. Thanks for any help.


  10. #10
    cheryl church
    Guest

    Re: Why can't I insert a row in my worksheet?

    You may have found one of the quirks about an array formula somewhere in the
    range that you are trying to add to.


    "susanholland3" <susanholland3@discussions.microsoft.com> wrote in message
    news:0B651741-585F-4B64-B734-D7E924025606@microsoft.com...
    > Well, I hate to be difficult but it's still not working. None of the
    > toolbars
    > are grayed out. The sheet is not protected (I double-checked) and the
    > cells
    > are not merged (I double-checked on that, too).
    >
    > But here's some more information I'm gleaning as I work with this: I have
    > created lists from the columns of this worksheet. On the column headings
    > where I have previously created lists, I click on the heading and get the
    > drop down list--as though the list is still there and part of the
    > worksheet.
    > I think that is why I can't add a row----a row cuts across those columns
    > that
    > think they still have lists.
    >
    > Does that make any sense? I think I need to somehow get rid of the lists
    > or
    > convince the worksheet that I don't care about that.
    >
    > Thanks for the efforts so far.....
    >
    > Susan
    > "Michael M" wrote:
    >
    >> OK
    >> Are the some of the toolbars greyed out ??
    >> If so, I would suggest that you're sheet is protected and needs to be
    >> unprotected.
    >> "The shift cells error " definately appears when there are merged cells
    >>
    >> HTH
    >> Michael M
    >>
    >> "susanholland3" wrote:
    >>
    >> > I don't have any merged cells, at least not that I know of or that I
    >> > intended. I have created lists from this worksheet and wonder if it
    >> > thinks I
    >> > still have a list open, but don't know how to correct for that.
    >> >
    >> > I tried locating the row above and below where I need it----it didn't
    >> > work.
    >> > Thanks for trying.
    >> >
    >> > "Michael M" wrote:
    >> >
    >> > > Hi Susan
    >> > > It sounds like the location your are trying to insert a row has
    >> > > merged cells
    >> > > in it.
    >> > > You will need to either unmerge the cells or go 1 row above or below
    >> > > the
    >> > > merged area.
    >> > >
    >> > > HTH
    >> > > Michael
    >> > >
    >> > > "susanholland3" wrote:
    >> > >
    >> > > > I am trying to insert a row of simple data into my worksheet. When
    >> > > > I do, I
    >> > > > get the following message:
    >> > > >
    >> > > > "This operation is not allowed. The operation is attempting to
    >> > > > shift cells
    >> > > > in a list on your worksheet."
    >> > > >
    >> > > > I don't understand this. I have created lists from the worksheet
    >> > > > but don't
    >> > > > have any now. Why does it think I have a list on my worksheet? And,
    >> > > > if I have
    >> > > > one, how do I remove it?
    >> > > >
    >> > > > I couldn't find anything on this in the knowledge base and am very
    >> > > > frustrated. Thanks for any help.




  11. #11
    Registered User
    Join Date
    05-09-2009
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    1

    Cool Re: Why can't I insert a row in my worksheet?

    The problem for me was that I had used the 'format as table' option in my spreadsheet, to alternate row colors. Apparently doing so creates a different kind of table where you cannot insert rows. To fix it, I had to copy my data into a new table and use the conditional formatting option, with a formula that worked in prior versions of Excel.

    Took me a while to figure out, surfed a lot of sites trying to find answers, nothing worked. It seems impossible to undo whatever is done when you choose the 'format as table' option. Everything was visible, nothing was locked, nothing was protected, nothing was merged.

  12. #12
    Registered User
    Join Date
    09-14-2009
    Location
    Albuquerque, New Mexico, USA
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Why can't I insert a row in my worksheet?

    I had the same problem and found what I think is the official fix: Convert your Excel table into a range of cells.

    Short answer: Right click within your data, point to Table, and then click Convert to Range.

    Details: http://office.microsoft.com/en-us/ex...675551033.aspx

  13. #13
    Registered User
    Join Date
    11-11-2013
    Location
    Maine
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Why can't I insert a row in my worksheet?

    I am having same problem but do not see any list on my sheet. Same problem is for all sheets in same work book. Any advice will be much appreciated.

  14. #14
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,146

    Re: Why can't I insert a row in my worksheet?

    mistic28,

    This is a 4 yr old thread. Its better to start your own. You will get faster replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  15. #15
    Registered User
    Join Date
    11-11-2016
    Location
    Los Angeles, CA
    MS-Off Ver
    2016
    Posts
    1

    Re: Why can't I insert a row in my worksheet?

    playing with the table settings so you don't have any Preserve or Adjust options checked should make it work when updating tables that have others within the same tab or worksheet.
    Best,

    Al

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1