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?

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?

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?