Insert row in a table on protected worksheet

Anonymous

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?

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 MVP Volunteer Moderator

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

Selection.EntireRow.Delete

0 comments

Answer accepted by question author

Anonymous

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.

0 comments

Answer accepted by question author

HansV 460.5K MVP Volunteer Moderator

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

0 comments

Answer accepted by question author

HansV 460.5K MVP Volunteer Moderator

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

0 comments

11 additional answers

Anonymous

2011-04-05T11:33:22+00:00

worked!!! Thanks a lot!!!
Was this answer helpful?

0 comments No comments