Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 2007 Find and Select

    Hi I am trying to write code/macro to look for the word production in column A. Once the word is found, I want to select the cell containing production and all the cells below it and delete it. The word production will always be in column A but it may be on a different row each time this code is run. Today it may be found in cell a585 but next week when I use it, it may be on cell a1245. Below is my code. The issue is that this is deleting the entire column instead of deleting the cell containing the word production and all cells below it. I think I am missing a line where the cell containing production is actually selected. Any asssitance is greatly appreciated.

    Columns("A:A").Select
    Selection.Find(What:="production", After:=Cells(1, 1), LookIn:=xlValues _
    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 766 Times in 700 Posts
    Give this a try.

    Code:
    Option Explicit
    Sub ProductionDelete()
    
    Cells.Find(What:="production", After:=Cells(1, 1), _
         LookIn:=xlValues, LookAt:=xlPart, _
         SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
         MatchCase:=False, SearchFormat:=False).Select
         
    Range(Selection, Selection.End(xlDown)).ClearContents
    
    End Sub
    Give this a try.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #3
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks..that worked. I now find that I have another issue with my code. I have the following code:

    ActiveWorkbook.Worksheets("Sheet5").Sort.SortField s.Clear
    ActiveWorkbook.Worksheets("Sheet5").Sort.SortField s.Add Key:=Range("C6:C1000") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet5").Sort
    .SetRange Range("A6:L594")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

    The issue I realize now is that this macro will be used on several worksheets within one workbook. The worksheet names will all be different. How can this be written where this code will work regardless of the worksheet name? I basically manually did the steps and let the macro record me, which is why some of my questions may seem elementary. Thanks again.
    Last edited by yjones72; 2011-01-19 at 07:08.

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,876
    Thanks
    0
    Thanked 80 Times in 76 Posts
    Something like:
    Code:
       Dim wks As Worksheet
       ' adjust if required
       Set wks = ActiveSheet
       
       With wks.Sort
          With .SortFields
             .Clear
             .Add Key:=wks.Range("C6:C1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
          End With
          .SetRange wks.Range("A6:L594")
          .Header = xlGuess
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
       End With
    Regards,
    Rory
    Microsoft MVP - Excel.

  6. #5
    Platinum Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,551
    Thanks
    7
    Thanked 225 Times in 213 Posts
    Don't forget to "set wks = Nothing" when you've finished - always clean up after yourself.

    cheers, Paul

  7. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,876
    Thanks
    0
    Thanked 80 Times in 76 Posts
    It will happen anyway when the code finishes.
    Regards,
    Rory
    Microsoft MVP - Excel.

  8. #7
    Platinum Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,551
    Thanks
    7
    Thanked 225 Times in 213 Posts
    Better to clean up during the macro so you don't consume more memory than necessary in a big macro.

    cheers, Paul

  9. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,876
    Thanks
    0
    Thanked 80 Times in 76 Posts
    If your macros are that long and memory intensive, then you have bigger things to worry about, IMO. Setting to Nothing is a duplication of work since VB(A) will do the same thing when the routine ends anyway (and more efficiently). Do you also clear your string variables the second you have finished with them? If not, why not?
    Although I recognise that there may be situations where you have need to remove object variables in a specific order (unlikely in VBA though) I would suggest that as long as your routines are kept short and single purpose wherever possible, you will see no benefit whatsoever from explicit setting to nothing and may even see a performance degradation.
    General rule of thumb in programming - avoid the words 'always' and 'better', unless you like an argument.
    Regards,
    Rory
    Microsoft MVP - Excel.

  10. #9
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts
    Hi Rory,

    How about a scenario where a runtime error causes code execution to stop? - I've never tested for this but always imagined (maybe incorrectly?) that in some such situations objects might be left floating around to cause problems later.

    By setting objvars = Nothing in an exit block that runs at the end of the sub but is also called from error handling code at the bottom of the procedure, that guarantees that even if the code errors, the objects are destroyed.

    Any rationale for going to that trouble, or would the objects reliably be destroyed in an error scenario too?

    Gary

  11. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,876
    Thanks
    0
    Thanked 80 Times in 76 Posts
    Gary,
    If you have an error handler then the code won't stop anyway, it will exit gracefully, so the question is moot, surely.
    Regards,
    Rory
    Microsoft MVP - Excel.

  12. #11
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts
    I suppose you're right!

    But one last question then: what about an unhandled error - in that situation, is there a risk of objects left instantiated, and if so, would Paul's suggested strategy of early destruction of object variables be useful? (or is the answer to that: always use error handling, and then the question is moot anyway!)

    Gary

  13. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,876
    Thanks
    0
    Thanked 80 Times in 76 Posts
    Well, you should of course always use error handling, but that aside, I have to confess I don't know for sure, though I assume the usual cleanup would occur prior to the code being allowed to stop. You would also only get any benefit if the error occurs after you explicitly set the variable to nothing.
    For my money, it serves no purpose, but it has always been a bone of contention amongst programmers. (a bit like walking into an Access forum and saying that unbound forms are best...)
    Regards,
    Rory
    Microsoft MVP - Excel.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •