Deleting specific Rows

I know this has been covered in one or two places (to say the least) however I cannot seem to match what I need. Essentially I am trying to get a Macro to allow me to delete rows where the text in Column L says "Inactive". All of the data is held in a Range from B5 to N100. I also have a numbering process that runs from B5 where Cell B5=1 and B6 = B5+1, when I remove rows that falls down and I get the dreaded ### Finally, just to make things a bit more tricky I want to unprotect the sheet, run the process then reapply the password, I have got that working...in a fashion but I wanted to integrate it into one process.

Any help would be really appreciated on this

Many Thanks

Paul

Welcome to the forum, Paul. :)

We have a "How To Delete Rows (http://www.xtremevbtalk.com/showthread.php?t=300757)" article in our code library which would be a good starting point. Post #4 in particular should be of interest. Once you have the row deletion working, post your code and then we can look at addressing any formula errors and how to unprotect/reprotect the worksheet.

Hope that helps...

That worked a treat Colin, did a bit of digging around and got the password release working too, thank you.

Now, can you point me in the direction where rather than the password being entered, I can get a dialog box to prompt the user to enter the password.

Also rather than delete the rows, I need to identify the rows dependent on the same criteria then copy and paste them into another sheet within the workbook, appending as I do more.

Thank you for your help

Here is the current code
Sub KillRows()
ActiveSheet.unprotect "annefjf"

Const sTOFIND As String = "Inactive"

Dim rngFound As Range, rngToDelete As Range
Dim sFirstAddress As String

Application.ScreenUpdating = False

With Sheet3.Range("JamesDATA")
Set rngFound = .Find( _
What:=sTOFIND, _
Lookat:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)

If Not rngFound Is Nothing Then
Set rngToDelete = rngFound

'note the address of the first found cell so we know where we started.
sFirstAddress = rngFound.Address

Set rngFound = .FindNext(After:=rngFound)

Do Until rngFound.Address = sFirstAddress
Set rngToDelete = Union(rngToDelete, rngFound)
Set rngFound = .FindNext(After:=rngFound)
Loop
End If
End With

If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete

Application.ScreenUpdating = True





ActiveSheet.Protect "annefjf"