CODEWITHSUNDEEP
excelvba
Max
Max

Reputation: 940

Deleting values in excel

I have an excel table, there are values in the cells, some of these cells have colored background, some don't have any background color. And some of these cells (colored or not) have the same values.

When I try to delete the contents of non-colored cells using "Find and Replace", it also deletes the colored cells with the same value. Is there a way to achieve this?

I just want to erase certain values only in non-colored cells.

Example file

Upvotes: 0

Views: 37

Answers (2)

Grade 'Eh' Bacon
Grade 'Eh' Bacon

Reputation: 3823

CTRL + H

Enter the value to delete in the 'find'. Specifically enter "FORMAT" -> "WHITE FILL". Instead of saying "NO VALUES" in the FORMAT field, it should now say "PREVIEW".

Then in the REPLACE field, type nothing. Leave the FORMAT field as "NO VALUES".

Replace all, and done.

Upvotes: 1

Looker
Looker

Reputation: 144

Try using .Interior.ColorIndex = None

So possibly something like:

If Cell(row, col).Interior.ColorIndex = None Then
    'Do FindAndReplace action here 
    '(Or try .ClearContents, as Omar suggested as a comment to your question)
End If

and adding a loop to get through your range.

It's difficult to help more than that without seeing what you're already tried, so if you could show what you've already done maybe others could help you better in your task?

Upvotes: 0

Related Questions