• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How can I delete only cells with a 6 in them

I have 5000 rows on numbers and I want to delete only the ones that have a 6 in them. Is there a command I can use to delete all of them without having to do them 1 at a time. The numbers will be

16,26,36,46,56,66
61,62,63,64,65

Thanks!
 
Select the Range
Ctrl+F
6
Find All
Ctrl+A
Close
Delete

or

Select the Range
Ctrl+F
6
Find All
Ctrl+A
Delete
Close
 
Hi Larry ,

Can you try doing a Find and Replace , specifying the search string as :

*6*

Narayan

I tried find and replace and it would not work because I'm not replacing it with anything and I couldn't figure out how to leave a blank cell The only thing I found that works were to filter the numbers in each column and clear entry
 
Hi Larry ,

When you do a Find and Replace , if the Replace With input is left blank , then Excel clears all cells of the input string.

Narayan
 
Hi:

If Ctrl +H Saves the day I guess you should go for that. If you still want a macro let me know I can write one for you.

Thanks
 
The following code borrowed from http://www.thespreadsheetguru.com/the-code-vault/2014/4/21/find-all-instances-with-vba
Does the trick

Code:
Sub Find_Clear_Values()

'PURPOSE: Clear all cells containing a specified values
'SOURCE: www.TheSpreadsheetGuru.com
'Modified by Hui to suit this post

Dim fnd As String, FirstFound As String
Dim FoundCell As Range, rng As Range
Dim myRange As Range, LastCell As Range

'What value do you want to find (must be in string form if searching strings)?
'Works ok for numbers as a number
  fnd = 6

Set myRange = ActiveSheet.UsedRange
Set LastCell = myRange.Cells(myRange.Cells.Count)
Set FoundCell = myRange.Find(what:=fnd, after:=LastCell)

'Test to see if anything was found
  If Not FoundCell Is Nothing Then
  FirstFound = FoundCell.Address
  Else
  GoTo NothingFound
  End If

Set rng = FoundCell

'Loop until cycled through all unique finds
  Do Until FoundCell Is Nothing
  'Find next cell with fnd value
  Set FoundCell = myRange.FindNext(after:=FoundCell)
   
  'Add found cell to rng range variable
  Set rng = Union(rng, FoundCell)
   
  'Test to see if cycled through to first found cell
  If FoundCell.Address = FirstFound Then Exit Do
   
  Loop

'Clear the Found cells 
rng.ClearContents
 
Exit Sub

'Error Handler
NothingFound:
  MsgBox "No values were found in this worksheet"

End Sub
 
Mr. Hui what if only want to change the color of the cell instead of delete it?

Such as if I want to change all the cells which have "6" into orange color?

I can not go back with the short cut Control+Z after I use this macro.

Is it possible to make a search bar which searches our requires and shows the cells which have got it in with a different color then let us move back?

Thanks in Advance.

Sincerly..
 
Simply replace
Code:
'Clear the Found cells
rng.ClearContents

With
Code:
'Clear the Found cells
'rng.ClearContents

'Highlight the Found cells
With rng.Interior
  .Pattern = xlSolid
  .Color = 49407
End With
 
Thank you sir.. It works but I have 2 problems.

First I can not undo it with Control+Z

Second in the attachment file even the values have not got "6" inside H5 and K5 cells show in yellow. These cells have "6" only in the formula not in the results.

(And F5 and J5 aren't be shown in yellow)
 

Attachments

  • dada.xlsm
    24.5 KB · Views: 1
Last edited:
What about Using Conditional Formatting

Select C5:N27
CF
New Rules
Use a Formula
=ABS(RIGHT(C5,1))=6
Apply a format

Apply
 
This formula gives error.

By the way I wanted to use for a moment, temporary search and see then remove. CF will do it permanently I guess.
 
Once a macro runs it clears the undo stack and so you cannot after the macro runs

Using CF will also fix the issue with not being to undo changes

If you use the formula
=and($Q$1<>"",ABS(RIGHT(C5,1))=$Q$1)

It will only apply a CF when there is a value in Q1
if Q1 is blank it will not apply any CF

You can use any cell instead of Q1 as well as any value, just change to suit
 
If you really want a VBA solution here is a much simpler solution

Code:
Sub Find_Values2()
Dim C As Range
For Each C In [C5:N27]
  If C.Text Like "*6*" Then C.Interior.Color = 49407
Next
End Sub

That replaces the entire previous subroutine and does all the cells correctly
 
Could you please check the following attached file sir?

It finds all 6s if I enter 6 in P1 but it doesn't find 16 if I enter 16.
 

Attachments

  • dada2.xlsx
    61.2 KB · Views: 2
Two issues

1. Change the CF Formula to:
=AND($P$1<>"",ABS(RIGHT(C5,len($p$1)))=$P$1)

2. Never apply CF's to whole columns
You had selected Columns A:N and hence applied 14 x 1000000 CF's
That can Kill spreadsheet performance
 
Change the section [C5:N27] to what ever range you require

PLEASE NOTE my previous comment

Never apply CF's to whole columns
You had selected Columns A:N and hence applied 14 x 1000000 CF's
That can Kill spreadsheet performance

Never (Rarely) Apply any formulas to whole Columns
 
Last edited:
Back
Top