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 / Programming / September 2007

Tip: Looking for answers? Try searching our database.

Code modification?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
fpd833 - 21 Sep 2007 15:44 GMT
I was graciously provided a macro that deletes all rows not having certain
values in col A. In the following line:

ArrNames = Array("Agt", "Agent")

Is it possible to add a wildcard to "Agt"? Those are just the first 3
letters of the lines I'm looking for and are followed by more text. None of
the rows are unique and are being deleted as a result of the routine.

I have pasted the provided code below, should the above make no sense.    

Thank you for your help!!

Ken

Sub Loop_Example()
     
   Dim Firstrow As Long
   Dim Lastrow As Long
   Dim Lrow As Long
   Dim CalcMode As Long
   Dim ViewMode As Long
   Dim ArrNames As Variant

   With Application
       CalcMode = .Calculation
       .Calculation = xlCalculationManual
       .ScreenUpdating = False
   End With
   
   With ActiveSheet
           .Select

       ViewMode = ActiveWindow.View
       ActiveWindow.View = xlNormalView

    .DisplayPageBreaks = False

       Firstrow = .UsedRange.Cells(7).Row
       Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
     
       ArrNames = Array(" Agt ID", "Agent Summary")
     
       For Lrow = Lastrow To Firstrow Step -1
         
           With .Cells(Lrow, "A")

               If Not IsError(.Value) Then

                   If IsError(Application.Match(.Value, ArrNames, 0)) Then
.EntireRow.Delete

               End If

           End With

       Next Lrow

   End With

   ActiveWindow.View = ViewMode
   With Application
       .ScreenUpdating = True
       .Calculation = CalcMode
   End With

End Sub
Barb Reinhardt - 21 Sep 2007 16:16 GMT
I believe you could use the LIKE operator to do this.

If YourString like "ABC*"   'Finds cells that start with ABC.

If YourString like "*ABC*"    'Finds cells with ABC anywhere in them
Signature

HTH,
Barb Reinhardt

> I was graciously provided a macro that deletes all rows not having certain
> values in col A. In the following line:
[quoted text clipped - 63 lines]
>
> End Sub
fpd833 - 21 Sep 2007 16:52 GMT
Thanks Barb! How/where would I work that into the code?

> I believe you could use the LIKE operator to do this.
>
[quoted text clipped - 69 lines]
> >
> > End Sub
 
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.