Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / November 2007

Tip: Looking for answers? Try searching our database.

Auto delete.....

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
gane - 20 Nov 2007 12:58 GMT
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 - 20 Nov 2007 14:56 GMT
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

> Hai I am maintaining a template which having values and blanks. I want to
> delete the blank rows. I wrote program as
[quoted text clipped - 16 lines]
> worksheet & workbook is password protected. the code above is not working for
> this case. Please anyone help me to solve my problem
gane - 21 Nov 2007 05:58 GMT
thanks itz working great..........

> One way:
>
[quoted text clipped - 44 lines]
> > worksheet & workbook is password protected. the code above is not working for
> > this case. Please anyone help me to solve my problem
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.