Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I would like to delete the contents of all cells in a selected range where they do not start with the numbers 672. Any ideas?
    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could run this macro:

    Code:
    Sub DeleteNot672()
      Dim oCell As Range
      For Each oCell In Selection.Cells
    	If Not oCell.Value Like "672*" Then
    	  oCell.ClearContents
    	End If
      Next oCell
    End Sub

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Hans

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='799367' date='22-Oct-2009 09:44']You could run this macro:

    Code:
    Sub DeleteNot672()
      Dim oCell As Range
      For Each oCell In Selection.Cells
    	If Not oCell.Value Like "672*" Then
    	  oCell.ClearContents
    	End If
      Next oCell
    End Sub
    [/quote]

    Thanks Hans, your solution works like a charm. Here is a new twist I have been given: How about deleting all of the contents of a cell that do not begin with the numbers 672 OR do not contain the letters "OBA" (sans the quotation marks of course)?
    Thanks again.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Change the line

    If Not oCell.Value Like "672*" Then

    to

    If Not oCell.Value Like "672*" Or Not oCell.Value Like "*OBA*" Then

    Test thoroughly on a copy of the workbook to make sure that it does what you want.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='799939' date='26-Oct-2009 07:22']Change the line

    If Not oCell.Value Like "672*" Then

    to

    If Not oCell.Value Like "672*" Or Not oCell.Value Like "*OBA*" Then

    Test thoroughly on a copy of the workbook to make sure that it does what you want.[/quote]

    Thanks, unfortunately, all of the contents of all of the cells are blanked out...

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I suspect that should be:
    Code:
    If Not oCell.Value Like "672*" And Not oCell.Value Like "*OBA*" Then MsgBox "no match"
    or you'll delete everything that doesn't start with 672 and contain OBA as well.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    That was what I expected, but it was exactly what you asked. Try

    If Not oCell.Value Like "672*" And Not oCell.Value Like "*OBA*" Then

    instead.

Posting Permissions

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