Nested IF problem

A

azu_daioh

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
 
A

azu_daioh

nevermind. I figured it out. I misplaced the x=x+1, it should be
after ELSE and before END IF.

Thanks anyway. :)
 
J

Jim Rech

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

--
Jim
| 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
|
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help enclosing a macro in With/End With 2
Am I doing something wrong here? 2
Is this possible? 1
VBA killing my named ranges 4
Delete rows. 3
Is this possible? 7
If, Then, Cut, Paste, Delete HELP 1
Optimize Slow function 3

Top