| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I delete a macro in Excel 2003? Button is greyed out. | Excel Discussion (Misc queries) | |||
| How can I delete a macro when the Delete button is not active? | Excel Worksheet Functions | |||
| Need macro to delete all text cells in an Excel range | Excel Worksheet Functions | |||
| How do i delete a macro in Excel 2003 when delete isn't highlight | Excel Discussion (Misc queries) | |||
| Can't find macro in an Excel file to delete it | Excel Discussion (Misc queries) | |||
Hybrid Mode
