I have a post with a subject of "Macro & VB101 and Excel Options". My
script has been added. Now, I want to edit the Module by adding a
deletion after it completes.

Description: Currently each cell is highlighted as RED if it equals
the cell above it.

New code: I will like to delete the RED cells and only keep the CLEAR
cells.

Resolution: None at this time.

Where should I start? Or Do anyone have the answer?

Ty,

In keeping with the same looping logic as your original macro:

Sub DeleteRedCells()
' NOTE: You must select the first cell in the column before running this macro

ScreenUpdating = False
Do While ActiveCell < ""
If ActiveCell.Interior.Color = RGB(255, 0, 0) Then
ActiveCell.Clear
End If
ActiveCell.Offset(1, 0).Select
Loop
ScreenUpdating = True
End Sub

-
HTH,
Bernie
MS Excel MVP


"Ty" wrote in message
...
I have a post with a subject of "Macro & VB101 and Excel Options". My
script has been added. Now, I want to edit the Module by adding a
deletion after it completes.

Description: Currently each cell is highlighted as RED if it equals
the cell above it.

New code: I will like to delete the RED cells and only keep the CLEAR
cells.

Resolution: None at this time.

Where should I start? Or Do anyone have the answer?

On Jul 30, 1:32*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Ty,

In keeping with the same looping logic as your original macro:

Sub DeleteRedCells()
* * ' NOTE: You must select the first cell in the column before running this macro

* *ScreenUpdating = False
* *Do While ActiveCell < ""
* * *If ActiveCell.Interior.Color = RGB(255, 0, 0) Then
* * * * *ActiveCell.Clear
* * * End If
* * * * ActiveCell.Offset(1, 0).Select
* *Loop
* *ScreenUpdating = True
End Sub

-
HTH,
Bernie
MS Excel MVP

"Ty" wrote in message

...



I have a post with a subject of "Macro & VB101 and Excel Options". *My
script has been added. *Now, I want to edit the Module by adding a
deletion after it completes.

Description: *Currently each cell is highlighted as RED if it equals
the cell above it.

New code: *I will like to delete the RED cells and only keep the CLEAR
cells.

Resolution: *None at this time.

Where should I start? *Or Do anyone have the answer?- Hide quoted text -

- Show quoted text -

Thanks for your response. I will give it a try. I'm assuming that
the "ActiveCell.Clear" is for the REMOVAL/DELETE of the cell. I
apologize for my ignorance at this point. I'm still trying to refresh
my memory.

..Clear removes all values and formatting. There are other options depending
on what you want to do....

Bernie


"Ty" wrote in message
...
On Jul 30, 1:32 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Ty,

In keeping with the same looping logic as your original macro:

Sub DeleteRedCells()
' NOTE: You must select the first cell in the column before running this
macro

ScreenUpdating = False
Do While ActiveCell < ""
If ActiveCell.Interior.Color = RGB(255, 0, 0) Then
ActiveCell.Clear
End If
ActiveCell.Offset(1, 0).Select
Loop
ScreenUpdating = True
End Sub

-
HTH,
Bernie
MS Excel MVP

"Ty" wrote in message

...



I have a post with a subject of "Macro & VB101 and Excel Options". My
script has been added. Now, I want to edit the Module by adding a
deletion after it completes.

Description: Currently each cell is highlighted as RED if it equals
the cell above it.

New code: I will like to delete the RED cells and only keep the CLEAR
cells.

Resolution: None at this time.

Where should I start? Or Do anyone have the answer?- Hide quoted text -

- Show quoted text -

Thanks for your response. I will give it a try. I'm assuming that
the "ActiveCell.Clear" is for the REMOVAL/DELETE of the cell. I
apologize for my ignorance at this point. I'm still trying to refresh
my memory.

On Jul 30, 3:31*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
.Clear removes all values and formatting. There are other options depending
on what you want to do....

Bernie

"Ty" wrote in message

...
On Jul 30, 1:32 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:





Ty,

In keeping with the same looping logic as your original macro:

Sub DeleteRedCells()
' NOTE: You must select the first cell in the column before running this
macro

ScreenUpdating = False
Do While ActiveCell < ""
If ActiveCell.Interior.Color = RGB(255, 0, 0) Then
ActiveCell.Clear
End If
ActiveCell.Offset(1, 0).Select
Loop
ScreenUpdating = True
End Sub

-
HTH,
Bernie
MS Excel MVP

"Ty" wrote in message

....

I have a post with a subject of "Macro & VB101 and Excel Options". My
script has been added. Now, I want to edit the Module by adding a
deletion after it completes.

Description: Currently each cell is highlighted as RED if it equals
the cell above it.

New code: I will like to delete the RED cells and only keep the CLEAR
cells.

Resolution: None at this time.

Where should I start? Or Do anyone have the answer?- Hide quoted text -

- Show quoted text -

Thanks for your response. *I will give it a try. *I'm assuming that
the "ActiveCell.Clear" is for the REMOVAL/DELETE of the cell. *I
apologize for my ignorance at this point. *I'm still trying to refresh
my memory.- Hide quoted text -

- Show quoted text -

OK. Great. I just figured out how to record a macro so I can see the
commands. I'm beginning to think like a programmer(i think). Unless
all of you just know this off the top of your head.

Here is my problem. I need to delete the whole row/line that actually
has the red cell. I found the following in the Record I made. I
don't think the "Selection.Delete Shift:=xlUp" will work.

Sub MacroTy()
'
' MacroMac Macro
' Macro recorded 7/30/2009
'

'
Selection.Cut
Application.CutCopyMode = False
Selection.ClearContents
Selection.Delete Shift:=xlUp

End Sub

Ty,

You can use:

Selection.EntireRow.Delete
or
ActiveCell.EntireRow.Delete

BUT note that when you do that, you need to adjust your activecell selection
process because deleting a cell or row changes the selection to what had
been the cell/row below at the time of deletion:


Do While ActiveCell < ""
If ActiveCell.Interior.Color = RGB(255, 0, 0) Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

That is why you will often see code that steps from the bottom up to the top
of a column when deletions are involved. For example:

For i = 10 to 1 Step -1
'deletion code here
Next i


HTH,
Bernie
MS Excel MVP


"Ty" wrote in message
...
On Jul 30, 3:31 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
.Clear removes all values and formatting. There are other options
depending
on what you want to do....

Bernie

"Ty" wrote in message

...
On Jul 30, 1:32 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:





Ty,

In keeping with the same looping logic as your original macro:

Sub DeleteRedCells()
' NOTE: You must select the first cell in the column before running this
macro

ScreenUpdating = False
Do While ActiveCell < ""
If ActiveCell.Interior.Color = RGB(255, 0, 0) Then
ActiveCell.Clear
End If
ActiveCell.Offset(1, 0).Select
Loop
ScreenUpdating = True
End Sub

-
HTH,
Bernie
MS Excel MVP

"Ty" wrote in message

...

I have a post with a subject of "Macro & VB101 and Excel Options". My
script has been added. Now, I want to edit the Module by adding a
deletion after it completes.

Description: Currently each cell is highlighted as RED if it equals
the cell above it.

New code: I will like to delete the RED cells and only keep the CLEAR
cells.

Resolution: None at this time.

Where should I start? Or Do anyone have the answer?- Hide quoted
text -

- Show quoted text -

Thanks for your response. I will give it a try. I'm assuming that
the "ActiveCell.Clear" is for the REMOVAL/DELETE of the cell. I
apologize for my ignorance at this point. I'm still trying to refresh
my memory.- Hide quoted text -

- Show quoted text -

OK. Great. I just figured out how to record a macro so I can see the
commands. I'm beginning to think like a programmer(i think). Unless
all of you just know this off the top of your head.

Here is my problem. I need to delete the whole row/line that actually
has the red cell. I found the following in the Record I made. I
don't think the "Selection.Delete Shift:=xlUp" will work.

Sub MacroTy()
'
' MacroMac Macro
' Macro recorded 7/30/2009
'

'
Selection.Cut
Application.CutCopyMode = False
Selection.ClearContents
Selection.Delete Shift:=xlUp

End Sub

On Jul 30, 5:19*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Ty,

You can use:

Selection.EntireRow.Delete
or
ActiveCell.EntireRow.Delete

BUT note that when you do that, you need to adjust your activecell selection
process because deleting a cell or row changes the selection to what had
been the cell/row below at the time of deletion:

*Do While ActiveCell < ""
* * *If ActiveCell.Interior.Color = RGB(255, 0, 0) Then
* * * * *ActiveCell.EntireRow.Delete
* * Else
* * * * *ActiveCell.Offset(1, 0).Select
* * *End If
*Loop

That is why you will often see code that steps from the bottom up to the top
of a column when deletions are involved. For example:

For i = 10 to 1 Step -1
'deletion code here
Next i

HTH,
Bernie
MS Excel MVP

"Ty" wrote in message

...
On Jul 30, 3:31 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:





.Clear removes all values and formatting. There are other options
depending
on what you want to do....

Bernie

"Ty" wrote in message

....
On Jul 30, 1:32 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:

Ty,

In keeping with the same looping logic as your original macro:

Sub DeleteRedCells()
' NOTE: You must select the first cell in the column before running this
macro

ScreenUpdating = False
Do While ActiveCell < ""
If ActiveCell.Interior.Color = RGB(255, 0, 0) Then
ActiveCell.Clear
End If
ActiveCell.Offset(1, 0).Select
Loop
ScreenUpdating = True
End Sub

-
HTH,
Bernie
MS Excel MVP

"Ty" wrote in message

....

I have a post with a subject of "Macro & VB101 and Excel Options". My
script has been added. Now, I want to edit the Module by adding a
deletion after it completes.

Description: Currently each cell is highlighted as RED if it equals
the cell above it.

New code: I will like to delete the RED cells and only keep the CLEAR
cells.

Resolution: None at this time.

Where should I start? Or Do anyone have the answer?- Hide quoted
text -

- Show quoted text -

Thanks for your response. I will give it a try. I'm assuming that
the "ActiveCell.Clear" is for the REMOVAL/DELETE of the cell. I
apologize for my ignorance at this point. I'm still trying to refresh
my memory.- Hide quoted text -

- Show quoted text -

OK. *Great. *I just figured out how to record a macro so I can see the
commands. *I'm beginning to think like a programmer(i think). *Unless
all of you just know this off the top of your head.

Here is my problem. *I need to delete the whole row/line that actually
has the red cell. *I found the following in the Record I made. *I
don't think the "Selection.Delete Shift:=xlUp" will work.

Sub MacroTy()
'
' MacroMac Macro
' Macro recorded 7/30/2009
'

'
* * Selection.Cut
* * Application.CutCopyMode = False
* * Selection.ClearContents
* * Selection.Delete Shift:=xlUp

End Sub- Hide quoted text -

- Show quoted text -

In response to the stepping from the bottom up to the top. Is that
why the "-1" is in the "ActiveCell.Offset(Offsetcount - 1, 0)". It is
not stepping from the bottom up but it is just doing what I call a 2
step(2 cells only). I see this while I'm doing the F8 and Watch.

p.s.-- I haven't had time to try the deletion as of yet. I really
appreciate the sharing of knowledge from an expert.

In response to the stepping from the bottom up to the top. Is that
why the "-1" is in the "ActiveCell.Offset(Offsetcount - 1, 0)". It is
not stepping from the bottom up but it is just doing what I call a 2
step(2 cells only). I see this while I'm doing the F8 and Watch.

p.s.-- I haven't had time to try the deletion as of yet. I really
appreciate the sharing of knowledge from an expert.

No. The -1 is so that the code compares two adjacent cells.

HTH,
Bernie
MS Excel MVP

On Jul 31, 9:30*am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
In response to the stepping from the bottom up to the top. *Is that
why the "-1" is in the "ActiveCell.Offset(Offsetcount - 1, 0)". *It is
not stepping from the bottom up but it is just doing what I call a 2
step(2 cells only). *I see this while I'm doing the F8 and Watch.
p.s.-- I haven't had time to try the deletion as of yet. *I really
appreciate the sharing of knowledge from an expert.

No. The -1 is so that the code compares two adjacent cells.

HTH,
Bernie
MS Excel MVP

Perfect. It works. 2 more questions.

What if I just wanted to delete rows that have the cell blank?

my answer: ActiveCell.Interior.Color = RGB(255, 0, 0) need to change
the RGB to the empty cell numbers(0, 0, 0)

What if I had another column such as column J as a condition to not
delete with my initials like TB?

my answer: If ActiveCell.Interior.Color = RGB(255, 0, 0) + column J is
blank. Don't really have answer but I'm trying to figure this out on
my own.

Thanks for your help....

Ty,

Something along the lines of

If ActiveCell.Interior.Color = RGB(255, 0, 0) Then
If Cells(ActiveCell.Row,10).Value = "" Then
ActiveCell.EntireRow.Clear
End If
End If

Column J is 10....

HTH,
Bernie
MS Excel MVP


"Ty" wrote in message
...
On Jul 31, 9:30 am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
In response to the stepping from the bottom up to the top. Is that
why the "-1" is in the "ActiveCell.Offset(Offsetcount - 1, 0)". It is
not stepping from the bottom up but it is just doing what I call a 2
step(2 cells only). I see this while I'm doing the F8 and Watch.
p.s.-- I haven't had time to try the deletion as of yet. I really
appreciate the sharing of knowledge from an expert.

No. The -1 is so that the code compares two adjacent cells.

HTH,
Bernie
MS Excel MVP

Perfect. It works. 2 more questions.

What if I just wanted to delete rows that have the cell blank?

my answer: ActiveCell.Interior.Color = RGB(255, 0, 0) need to change
the RGB to the empty cell numbers(0, 0, 0)

What if I had another column such as column J as a condition to not
delete with my initials like TB?

my answer: If ActiveCell.Interior.Color = RGB(255, 0, 0) + column J is
blank. Don't really have answer but I'm trying to figure this out on
my own.

Thanks for your help....