Extending a selection

[h2fcell]

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

I’ve 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.

Here’s 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?

 

[Jim Rech]

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
| 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
|
| I’ve 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.
|
| Here’s 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?

 

[h2fcell]

Nice.
Thanks Jim that work for me.