Auto delete.....

[Guest]

Hai I am maintaining a template which having values and blanks. I want to
delete the blank rows. I wrote program as
Sub DelRows()
Dim c As Range

strfind = InputBox("enter value to find and delete")

With Worksheets(1).Range("A:A")
Do
Set c = .Find(strfind, LookIn:=xlValues, lookat:=xlPart, _
MatchCase:=False)
If c Is Nothing Then Exit Do
c.EntireRow.Delete
Loop
End With
End Sub

Its working very fine. but the fact is i had locked certain cells and my
worksheet & workbook is password protected. the code above is not working for
this case. Please anyone help me to solve my problem

 

[JE McGimpsey]

One way:

You can unprotect your worksheet in the macro, then protect it again:

Public Sub DelRows()
Const sPWORD As String = "drowssap"
Dim c As Range
Dim strfind As String

strfind = InputBox("enter value to find and delete")

With Worksheets(1)
.Unprotect Password:=sPWORD
With .Range("A:A")
Do
Set c = .Find( _
What:=strfind, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If c Is Nothing Then Exit Do
c.EntireRow.Delete
Loop
End With
.Protect Password:=sPWORD
End With
End Sub

 

[Guest]

thanks itz working great..........

One way:

You can unprotect your worksheet in the macro, then protect it again:

Public Sub DelRows()
Const sPWORD As String = "drowssap"
Dim c As Range
Dim strfind As String

strfind = InputBox("enter value to find and delete")

With Worksheets(1)
.Unprotect Password:=sPWORD
With .Range("A:A")
Do
Set c = .Find( _
What:=strfind, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If c Is Nothing Then Exit Do
c.EntireRow.Delete
Loop
End With
.Protect Password:=sPWORD
End With
End Sub