|
Excel Delete VB Macro
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? |
Excel Delete VB Macro
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? |
Excel Delete VB Macro
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. |
Excel Delete VB Macro
..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. |
Excel Delete VB Macro
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 |
Excel Delete VB Macro
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 |
Excel Delete VB Macro
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. |
Excel Delete VB Macro
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 |
Excel Delete VB Macro
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.... |
Excel Delete VB Macro
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.... |
| All times are GMT +1. The time now is 08:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com