Can anyone take a look at the code please and help me figure out why
it's skipping every other "elfseif" statement?
lets say I have these values on each row (its a copy and paste from a
dumb terminal) which I'm trying to reformat in Excel because we can't
import the data directly into Excel (or at least our programmers won't
provide us with instructions).
VIEW 2.0
COMMAND ===>
*********************************************************** TOP OF
DATA
.SARPAGE
1
.
.ROUTED ALL
.REPORT
.PROGRAM
. *** FOR MONTH
. DOCTOR
. LICENSE
. NUMBER
. -------- --------------------------------
***********************************************************BOTTOM OF
DATA
For some reason it will delete the 1, 3, 5, 7, etc rows matching the
values indicated in the IF statements.
Thank you,
Sharon
---------
Sub delete_unwanted_row()
Dim x As Long
Dim lRow As Long
x = 1 'row
lRow = InputBox("Enter Last Row")
For oRow = 1 To lRow
If Cells(x, 1).Value Like "*" & "VIEW" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
ElseIf Cells(x, 1).Value Like "*" & "COMMAND" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
ElseIf Cells(x, 1).Value Like "*" & "OF DATA" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
ElseIf Cells(x, 1).Value Like "*" & "SARPAGE" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
ElseIf Cells(x, 1).Value Like "*" & "ROUTED" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
ElseIf Cells(x, 1).Value Like "*" & "REPORT" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
ElseIf Cells(x, 1).Value Like "*" & "PROGRAM" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
ElseIf Cells(x, 1).Value Like "*" & "MONTH" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
ElseIf Cells(x, 1).Value Like "*" & "DOCTOR" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
ElseIf Cells(x, 1).Value Like "*" & "LICENSE" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
ElseIf Cells(x, 1).Value Like "*" & "NUMBER" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
ElseIf Cells(x, 1).Value Like "*" & "----" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
End If
x = x + 1
Next oRow
End Sub
azu_daioh@yahoo.com - 12 Sep 2007 22:11 GMT
nevermind. I figured it out. I misplaced the x=x+1, it should be
after ELSE and before END IF.
Thanks anyway. :)
Jim Rech - 12 Sep 2007 22:12 GMT
Excel gets confused with a For Each (row) when rows are being deleted.
Rather than
For oRow = 1 To lRow
try
For lRow to 1 Step -1

Signature
Jim
| Can anyone take a look at the code please and help me figure out why
| it's skipping every other "elfseif" statement?
[quoted text clipped - 80 lines]
|
| End Sub