MrExcel.com - Your One Stop for Excel Tips & Solutions

RYAN.. here is my example.. i need to add a line to delete rows containing 25

Posted by mike k on July 17, 2000 11:44 AM

Range("B2").Select
Do Until ActiveCell = ""
If ActiveCell.Formula = "99" Then
ActiveCell.Formula = "CATCHALL"
ElseIf ActiveCell = "21" Then
ActiveCell.Formula = "EDWARD SMITH"
ElseIf ActiveCell = "22" Then
ActiveCell.Formula = "MELISSA WOLTERS"
ElseIf ActiveCell = "23" Then
ActiveCell.Formula = "JEFF SCHEIN"
ElseIf ActiveCell = "24" Then
ActiveCell.Formula = "LESLIE PUSHKIN"
ElseIf ActiveCell = "26" Then
ActiveCell.Formula = "KRISTIE HOFFMAN"
ElseIf ActiveCell = "27" Then
ActiveCell.Formula = "BARBARA CAIN"
ElseIf ActiveCell = "28" Then
ActiveCell.Formula = "BOB LINDSAY"
ElseIf ActiveCell = "29" Then
ActiveCell.Formula = "WARREN ROACH"
ElseIf ActiveCell = "30" Then
ActiveCell.Formula = "SIOBHAN KELLY"
ElseIf ActiveCell = "31" Then
ActiveCell.Formula = "KIM O'CONNELL"
ElseIf ActiveCell = "32" Then
ActiveCell.Formula = "HEATHER BEALLE"
ElseIf ActiveCell = "33" Then
ActiveCell.Formula = "LEN DANO"
ElseIf ActiveCell = "34" Then
ActiveCell.Formula = "BEVERLY JULIG"
ElseIf ActiveCell = "35" Then
ActiveCell.Formula = "DIMITRIOS VOULGARIS"
ElseIf ActiveCell = "37" Then
ActiveCell.Formula = "SUSAN CADIGAN"
ElseIf ActiveCell = "39" Then
ActiveCell.Formula = "PREMIUM"
End If
ActiveCell.Offset(1, 0).Select
Loop

Posted by Ryan on July 17, 0100 12:07 PM

Mike,

When you delete a row with 25, you can't have the next row be selected, b/c you will skip a row, when all cells are deleted. I would put this in at the end:

ElseIf ActiveCell = "25"
Selection.EntireRow.Delete
' Declare a boolean varible
Skip = False
End if

If Skip = True Then ActiveCell.Offset(1,0).Select

At the top of the do loop Set Skip equal to true.

Other then that, everything looks fine! Let me know how it works.

Ryan

Posted by mike k on July 17, 0100 12:20 PM

i am confused with what goes on top the do loop??


Posted by Ryan on July 17, 0100 12:51 PM

Dim Skip As Boolean


Do Until ActiveCell = ""
Skip = True
....
...
Loop

How's that?

Ryan

Posted by mike k on July 17, 0100 1:44 PM

THANKS IT WORKED