PDA

View Full Version : [SOLVED] Macro deletes text only in No Fill cells



k0st4din
12-11-2013, 10:49 PM
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
12-12-2013, 12:18 AM
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
12-13-2013, 02:24 PM
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
12-14-2013, 02:48 AM
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
12-14-2013, 03:03 AM
hi there.

just added the missing range object.


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

k0st4din
12-14-2013, 07:39 AM
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
12-14-2013, 03:31 PM
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
12-14-2013, 03:41 PM
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
12-15-2013, 12:57 AM
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.