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 460.5K 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 460.5K 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 460.5K 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: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-04-05T10:06:41+00:00

    Hi HansV,

    I am stuck again but now at delete function. I have a column named serial number. The insert row macro (above) will create the rows and they will get the serial number using row() function. Now I am trying a macro that should delete the row only if it has the serial number i.e. only those rows which were added by previous macro could be deleted and not the one with column name or any other blank row from the worksheet.

    Is there a way to do this?

    Was this answer helpful?

    0 comments