Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Call me simplistic:
I have the following code in Excel 2007 that works well. 1. Range("A10").Select 2. Cells.Replace What:="Expired", Replacement:="=NA()", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False 3. Selection.SpecialCells(xlCellTypeFormulas, 16).Offset(0, 1).Select 4. Selection.FormulaR1C1 = "=NA()" 5. Range("A10").Select 6. Selection.SpecialCells(xlCellTypeFormulas, 16).Select 7. Selection.Delete Shift:=xlUp 8. Range("A1").Select Ive added line numbers for this discussion. The goal of this macro is to get rid of €śExpired€ť entries and its coresponding date in the cell to the right by clearing the cells and moving up the cells below it. Below is a sample of what the sheet looks like. NEW YORK CHICAGO Name Date Name Date Tom 7/12/2008 Sue 7/8/2008 Bill 4/5/2007 Expired 6/5/2008 Jill 4/5/2008 Bob 5/10/2007 Alison 7/12/2008 Sam 5/12/2008 Expired 6/1/2007 Expired 4/12/2008 Julie 5/9/2008 Expired 12/11/2007 Kevin 5/12/2007 Andrew 7/25/2008 Expired 3/20/2007 Expired 4/5/2008 Step 2 replaces all cells with value €śExpired€ť to €ś=NA()€ť which Excel considers a formula error. Step 3 finds all those errors and selects only the cells to the right. Step 4 places errors in the cells selected by step 3. Heres where I want to simplify the macro if possible. I would like step three to select the errors and one cell to the right. If I could do that in one step I would eliminate steps 4, 5, & 6. I need - Selection.SpecialCells(xlCellTypeFormulas, 16).Select to include one cell to the right. Anyone know how it's done? |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
You could slim it down like this:
Sub DelExpired() Dim DelRg As Range Cells.Replace What:="Expired", Replacement:="=NA()" Set DelRg = Cells.SpecialCells(xlCellTypeFormulas, 16) Union(DelRg, DelRg.Offset(0, 1)).Delete xlUp End Sub -- Jim "h2fcell" wrote in message ... | Call me simplistic: | I have the following code in Excel 2007 that works well. | | 1. Range("A10").Select | 2. Cells.Replace What:="Expired", Replacement:="=NA()", LookAt:= _ | xlPart, SearchOrder:=xlByRows, MatchCase:=False, | SearchFormat:=False, _ | ReplaceFormat:=False | 3. Selection.SpecialCells(xlCellTypeFormulas, 16).Offset(0, 1).Select | 4. Selection.FormulaR1C1 = "=NA()" | 5. Range("A10").Select | 6. Selection.SpecialCells(xlCellTypeFormulas, 16).Select | 7. Selection.Delete Shift:=xlUp | 8. Range("A1").Select | | Ive added line numbers for this discussion. The goal of this macro is to | get rid of €śExpired€ť entries and its coresponding date in the cell to the | right by clearing the cells and moving up the cells below it. Below is a | sample of what the sheet looks like. | | NEW YORK CHICAGO | Name Date Name Date | Tom 7/12/2008 Sue 7/8/2008 | Bill 4/5/2007 Expired 6/5/2008 | Jill 4/5/2008 Bob 5/10/2007 | Alison 7/12/2008 Sam 5/12/2008 | Expired 6/1/2007 Expired 4/12/2008 | Julie 5/9/2008 Expired 12/11/2007 | Kevin 5/12/2007 Andrew 7/25/2008 | Expired 3/20/2007 | Expired 4/5/2008 | | Step 2 replaces all cells with value €śExpired€ť to €ś=NA()€ť which Excel | considers a formula error. | | Step 3 finds all those errors and selects only the cells to the right. | | Step 4 places errors in the cells selected by step 3. | | Heres where I want to simplify the macro if possible. I would like step | three to select the errors and one cell to the right. If I could do that in | one step I would eliminate steps 4, 5, & 6. | I need - Selection.SpecialCells(xlCellTypeFormulas, 16).Select to include | one cell to the right. Anyone know how it's done? |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Nice.
Thanks Jim that work for me. "Jim Rech" wrote: You could slim it down like this: Sub DelExpired() Dim DelRg As Range Cells.Replace What:="Expired", Replacement:="=NA()" Set DelRg = Cells.SpecialCells(xlCellTypeFormulas, 16) Union(DelRg, DelRg.Offset(0, 1)).Delete xlUp End Sub -- Jim "h2fcell" wrote in message ... | Call me simplistic: | I have the following code in Excel 2007 that works well. | | 1. Range("A10").Select | 2. Cells.Replace What:="Expired", Replacement:="=NA()", LookAt:= _ | xlPart, SearchOrder:=xlByRows, MatchCase:=False, | SearchFormat:=False, _ | ReplaceFormat:=False | 3. Selection.SpecialCells(xlCellTypeFormulas, 16).Offset(0, 1).Select | 4. Selection.FormulaR1C1 = "=NA()" | 5. Range("A10").Select | 6. Selection.SpecialCells(xlCellTypeFormulas, 16).Select | 7. Selection.Delete Shift:=xlUp | 8. Range("A1").Select | | Ive added line numbers for this discussion. The goal of this macro is to | get rid of €śExpired€ť entries and its coresponding date in the cell to the | right by clearing the cells and moving up the cells below it. Below is a | sample of what the sheet looks like. | | NEW YORK CHICAGO | Name Date Name Date | Tom 7/12/2008 Sue 7/8/2008 | Bill 4/5/2007 Expired 6/5/2008 | Jill 4/5/2008 Bob 5/10/2007 | Alison 7/12/2008 Sam 5/12/2008 | Expired 6/1/2007 Expired 4/12/2008 | Julie 5/9/2008 Expired 12/11/2007 | Kevin 5/12/2007 Andrew 7/25/2008 | Expired 3/20/2007 | Expired 4/5/2008 | | Step 2 replaces all cells with value €śExpired€ť to €ś=NA()€ť which Excel | considers a formula error. | | Step 3 finds all those errors and selects only the cells to the right. | | Step 4 places errors in the cells selected by step 3. | | Heres where I want to simplify the macro if possible. I would like step | three to select the errors and one cell to the right. If I could do that in | one step I would eliminate steps 4, 5, & 6. | I need - Selection.SpecialCells(xlCellTypeFormulas, 16).Select to include | one cell to the right. Anyone know how it's done? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Extending a range selection | Excel Programming | |||
Extending Selection of Column to the next Column to the Right | Excel Programming | |||
Extending Row() | Excel Worksheet Functions | |||
extending selection | Excel Discussion (Misc queries) | |||
Extending Selection | Excel Programming |