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
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)
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)
Sub delPOBoxRows()
Dim rang As Range
Set rang = Range("A1")
Do While rang <> ""
If UCase(rang.Offset(0, 2)) Like UCase("P*O*Box*") Then
Set rang = rang.Offset(-1, 0)
Rows(rang.Row + 1).Delete
End If
Set rang = rang.Offset(1, 0)
Loop
End Sub
Display More
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)
Wow,
All this time, I could have used the * wildcard in my strings?
Thanks for the tip Jim. I had no idea.
Are there any other wildcards, such as the "?"
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...
Jong and Jmhans,
Thank you very much for help.
Don’t have an account yet? Register yourself now and be a part of our community!