Macro, delete some rows

  • Hi All,


    Please Help.


    I have a big customer’s records in worksheet and I would like to have a macro which deletes rows that have “PO Box” (they can be P.O. Box or P O Box or POBOX) in address column.


    Thank You

  • Best thing to do in this situation is somethign like this:


    (Assuming PO-Box is entered in column A)



    Code
    For each cell in worksheets("A:A")
         if instr(UCase(cell.value), "PO BOX") <> 0 or instr(UCase(cell.value), "P.O. BOX") <> 0 or _
    instr(UCase(cell.value), "P O BOX") <> 0 or instr(UCase(cell.value), "POBOX") <> 0 then
    
    
    cell.entirerow.delete
    
    
    next cell




    You can also use regular expressions for this, although since there are so many ways to write POBOX, your expression could get quite complicated.


    If your interested in doing it with regular expressions, here is a good read:
    http://www.aivosto.com/vbtips/regex.html

  • Say that the "Address" Column is Column C (just a random choice)



    This worked on a simple test.


    It assumes that there is always data in the "A" column (if not, adjust the Range("A1") near beginning)


    If addresses are not in "C" column, adjust UCase(rang.Offset(0, 2))
    so that it offsets as many columns away from the first column as it is (i.e. Column D is 3 cols from A, column E is 4 cols. from A, etc...) Of course, if you changed the "A", this will also need to be adjusted...


    Hope this helps!



    EDIT: This can be made much simpler if there's always something in the Address column. If that's the case, change the Range("A1") to wherever the address column is, and then change the UCase(rang.Offset(0, 2)) to simply:
    UCase(rang)

  • Wildcards in vba that I know are:


    * (0+ length string, text or number)
    # (1 char. number)
    ? (1 char. text/number)


    For more info - while in vba, do a search for "wildcards"


    There's also some [charlist] features that will check only for certain characters...

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!