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: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-03-31T04:40:56+00:00

    I don't know macro programming and vba coding. Can you please provide a macro which will copy the row I want it to and insert it after the last row in the array? I will convert the table into range as the table feature is of no use. Can the macro identify the last row & then copy paste the row below it?

    Just to give you idea about what I am thinking (not sure whether such thing is possible). I want to hide the first row in the array then protect the worksheet so that no one can put any values in that row. Then using macro I want  to copy this hidden row and paste it at the bottom of the array so whenever user runs the macro he will get one blank row with formulas to fill the details.

    Was this answer helpful?

    0 comments