Insert row in a table on protected worksheet

Anonymous
2011-03-29T09:40:23+00:00

Excel 2007 tables have this feature to add row by pressing the keyboard tab on last cell of last row. But if the workbook is protected the error message is displayed saying "cannot use this table feature with protected worksheets".

I want to enable add row feature on protected worksheet. Is it possible?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
HansV 462.7K Reputation points MVP Volunteer Moderator
2011-04-05T11:29:33+00:00

If you want to delete the entire row(s) of the selection, you can use

Selection.EntireRow.Delete

Was this answer helpful?

0 comments
Answer accepted by question author
Anonymous
2011-04-01T05:35:32+00:00

Thanks Hans,

I have done it using another way. I have hiddent the last row of the array and I used row function to get row number of that row. Whenever new row is added the row number returned is the number of row below newly added row. I kept the number in other cell and in the macro using value property i take that number add 1 to it and insert the copied row.

You are very good at vb programming ..thanks again.

Was this answer helpful?

0 comments
Answer accepted by question author
HansV 462.7K Reputation points MVP Volunteer Moderator
2011-03-31T14:53:09+00:00

Try this:

Sub CopRow()

    Dim r As Long

    ActiveSheet.Unprotect

    r = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByRows, _

        SearchDirection:=xlPrevious).Row + 1

    Rows(34).Copy

    Rows(r).Insert

    ActiveSheet.Protect

End Sub

Was this answer helpful?

0 comments
Answer accepted by question author
HansV 462.7K Reputation points MVP Volunteer Moderator
2011-03-31T05:51:52+00:00

It's great that you managed to create a macro yourself!

In many situations, it is not necessary to select ranges in VBA. Your macro could be shortened to

Sub CopyRow()

    ActiveSheet.Unprotect

    Rows(34).Copy

    Rows(54).Insert

    ActiveSheet.Protect

End Sub

Was this answer helpful?

0 comments

11 additional answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2011-03-30T04:33:22+00:00

    Thanks HansV,

    I have already checked insert rows check box. Problem is the rows of table contain formulas at various cells. The tab gives new row in the table and it automatically copies the formulas. That feature goes off when the worksheet is protected and there is no way to get it back.

    Another way was to copy the existing rows with formula and insert but that feature also gets disabled after protecting. Just adding the row without formulas is of no use. Can there be a macro which will enable this tab functionaliy or any other macro which will copy and append the existing row to the table but with formula & blank values?

    Was this answer helpful?

    0 comments