Excel Delete VB Macro

  • Thread starter Thread starter Ty
  • Start date Start date
T

Ty

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,

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










- Show quoted text -
Click to expand...

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,

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










- Show quoted text -
Click to expand...

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










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 -
Click to expand...

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


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

Bernie










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 -
Click to expand...

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







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 -
Click to expand...

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.
Click to expand...
p.s.-- I haven't had time to try the deletion as of yet. I really
appreciate the sharing of knowledge from an expert.
Click to expand...

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

HTH,
Bernie
MS Excel MVP
 
No. The -1 is so that the code compares two adjacent cells.

HTH,
Bernie
MS Excel MVP
Click to expand...

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


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

HTH,
Bernie
MS Excel MVP
Click to expand...

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....
 
Back
Top