# Excel macro must find row number



## aevert (Oct 10, 2005)

Scenario: the spreadsheet is protected and the user can only delete a row by using a macro. The macro will unprotect the spreadsheet, delete the row, then protect it again. The spreadsheet has description and header rows at the top and summary rows at the bottom. The user may only delete data rows in the middle.

Task: the macro must determine the current row number and verify that it is legal to delete that row.

I am a newbie to VB and can't find a function which will do this for me. I thought that RW would help, but I can't get it to work. Any help will be much appreciated.

Here are related processes that don't trouble me: creating and editing macros, locking cells, protecting sheets, deleting current row, using the IF function.

Thanks. Alvin.


----------



## Rollin_Again (Sep 4, 2003)

To find the current row you simply use *Activecell.Row*

Rollin


----------



## donnj (Sep 16, 2005)

add these 2 lines of code to your macro: 

This line of code goes before the first line. 
Application.ScreenUpdating = False

and 

'This line of code goes before the last line (just before End Sub).
Application.ScreenUpdating = True

OK, so Line 1 would be most likely be the column headers and therefore "Illegal"
to delete (or off limits to the user) and the Summary Row down below would also be "Illegal" to delete. Then everything in between would be "Legal" or allowable for deletion, correct? 

Just wondering, why not unlock the Middle cells and keep all the Header & Summary rows locked, then protect the sheet (with a password) and "Check" which actions/boxes you will allow the User to use,
i.e.: Select locked cells, Select unlocked cells, and Delete rows. 
Note: you may not want the User to even select locked cells, but that's up to you guys. Also, I am using Excel 2002 and everyone else here is using 2003, but not sure when that additional feature came about. So, older versions may not allow you to select which actions are allowable

please let me know if this helps


----------



## bomb #21 (Jul 1, 2005)

aevert said:


> Scenario: the spreadsheet is protected and the user can only delete a row by using a macro. The macro will unprotect the spreadsheet, delete the row, then protect it again.


That's it? All users have to/can do is delete rows? There must be more to it. 

Anyways, see in the attached file, including :

Sub Macro1()
If ActiveCell.Row < 3 Then
MsgBox "Cannot delete first two rows."
ElseIf ActiveCell.Row = Range("A65536").End(xlUp).Row Then
MsgBox "Cannot delete summary row."
Else
'ActiveSheet.Unprotect
ActiveCell.EntireRow.Delete
'ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End Sub

Note the 2 remmed lines (i.e. apostrophes in front). You don't have to use continual protecting/re-protecting _if_ you use the UserInterfaceOnly method in the ThisWorkbook module, i.e.:

Private Sub Workbook_Open()
ActiveWorkbook.Sheets("Sheet1").Protect UserInterfaceOnly:=True
End Sub

The "selective protection" donnj mentions is only in Excel XP and later. Post back if you need more info.

Rgds,
Andy


----------

