Macro deletes text only in No Fill cells

[k0st4din]

Hi friends,
in searching the internet I found many examples but not found a solution to my problem.
I have a range of cells (Table), some cells are colored, others are colorless (No fill). Is it possible to make a macro that deletes written only in these unstained cells?

[ashleyuk1984]

Highlight your table.... Then run the macro.


Sub DeleteValue()
Dim cell As Range
For Each cell In Selection
If cell.Interior.ColorIndex < 0 Then
cell.Value = ""
End If
Next cell
End Sub

This will delete the contents of the cells which you have highlighted that DOESN'T have colour.
Hope this helps.

[k0st4din]

Hello and thank you very much for your cooperation.
I slightly modified the code to this, but again works very well.
I have one more question, what I should add that before you delete copy the information in Sheet2

Sub DeleteValue()
Dim cell As Range
For Each cell In Range("E8:AM175")
If cell.Interior.ColorIndex < 0 Then
cell.Value = ""
End If
Next cell
End Sub

[ashleyuk1984]

Hope this helps.


Sub DeleteValue()
Dim cell As Range

Range("E8:AM175").Copy
Worksheets("Sheet2").Paste

For Each cell In Range("E8:AM175")
If cell.Interior.ColorIndex < 0 Then
cell.Value = ""
End If
Next cell
End Sub

[mancubus]

hi there.

just added the missing range object.


Range("E8:AM175").Copy Worksheets("Sheet2").Range("A1")

[k0st4din]

Is there a mistake somewhere because I want to just copy the cell values ​​are <0, ie "No Fill", while in the case of my copy and the yellow cells?
I tried to put this condition to sift information, but does not work

Sub Delete1Value() Dim cell As Range
If cell.Interior.ColorIndex < 0 Then
Range("A1:G12").Copy Worksheets("Sheet2").Range("A1")

For Each cell In Range("A1:G12")
If cell.Interior.ColorIndex < 0 Then
cell.Value = ""
End If
Next cell
End Sub

[ashleyuk1984]

Sorry, I'm having trouble reading your explanation.
Do you want just the white cells (no fill) on sheet 2?? Or just the yellow cells on sheet 2 ??

[mancubus]

Sub Delete1Value()

Dim cll As Range

Worksheets("Sheet1").Range("A1:G12").Copy Worksheets("Sheet2").Range("A1")

For Each cll In Worksheets("Sheet2").Range("A1:G12")
If cll.Interior.ColorIndex <> xlNone Then cll.Clear
Next cll

For Each cll In Worksheets("Sheet1").Range("A1:G12")
If cll.Interior.ColorIndex = xlNone Then cll.Clear
Next cll


End Sub

[k0st4din]

Hello to you both,
to in the above post I wrote I just colorless cells (<0, No Fill).
mancubus (http://www.vbaexpress.com/forum/member.php?37987-mancubus) thank you've made a macro work.
Thank you very much. If my boss he wished something would ask you again.
I wish you all good.