Help Exiting Loop

[Giri]

Hi Guys,

With the following code, I can't seem to get out of the two loops below which I have made comments next to.

Therefore the macro doesn't go onto the next loop. I want the two loops to end when it encounters a blank cell. Everything else, seems to be working alright.

If anyone can help with this, I'd greatly appreciate it!!

Thank you!

Giri

[VBA]
Option Explicit

Public Sub AmpLife()

Dim c As Range
Dim n As Integer
Dim x As Integer
Dim row As Integer
Dim stringArray(3) As String
Dim searchString As String

'Application.ScreenUpdating = False


row = Range("D2").End(xlDown).row
n = 0

For Each c In Range("D2" & row)


For x = 0 To 2
searchString = c.Value
stringArray(x) = Split(searchString, " ")(n)
n = n + 1
Next x

c.Select

On Error Resume Next

If stringArray(0) <> "AMP" Or stringArray(1) <> "Life" Or stringArray(2) <> "Ltd" Then

c.EntireRow.Delete
stringArray(0) = Split(ActiveCell.Value, " ")(0)
stringArray(1) = Split(ActiveCell.Value, " ")(1)
stringArray(2) = Split(ActiveCell.Value, " ")(2)

Do While stringArray(0) <> "AMP" Or stringArray(1) _ 'THIS LOOP
<> "Life" Or stringArray(2) <> "Ltd" And IsEmpty(ActiveCell) = False


c.Select
ActiveCell.EntireRow.Delete
stringArray(0) = Split(ActiveCell.Value, " ")(0)
stringArray(1) = Split(ActiveCell.Value, " ")(1)
stringArray(2) = Split(ActiveCell.Value, " ")(2)

Loop



End If

n = 0

Next c

On Error GoTo 0





row = Range("E2").End(xlDown).row

For Each c In Range("E2:E" & row)

c.Select
stringArray(0) = Split(ActiveCell.Value, " ")(0)

If stringArray(0) <> "Internal" Then
c.EntireRow.Delete
stringArray(0) = Split(ActiveCell.Value, " ")(0)
End If

Do While stringArray(0) <> "Internal" 'THIS LOOP
ActiveCell.Select
ActiveCell.EntireRow.Delete
stringArray(0) = Split(ActiveCell.Value, " ")(0)
Loop
Next c

'Application.ScreenUpdating = True

End Sub
[/VBA]

[Paul_Hossler]

Not sure about the logic, but

1. Maybe Parens ?

[VBA]
Do While _
(stringArray(0) <> "AMP" Or _
stringArray(1) <> "Life" Or _
stringArray(2) <> "Ltd") And _
Not IsEmpty(ActiveCell)
[/VBA]


2. I did notice that you select a cell, and delete the row it's in. Next you refer to the ActiveCell. I was wondering if that is what you want to do since the ActiveCell would change

[VBA]
c.Select
ActiveCell.EntireRow.Delete
stringArray(0) = Split(ActiveCell.Value, " ")(0)
stringArray(1) = Split(ActiveCell.Value, " ")(1)
stringArray(2) = Split(ActiveCell.Value, " ")(2)
[/VBA]


3. You probably don't need to select the cell to act on it

[VBA]
' c.Select
c.EntireRow.Delete
stringArray(0) = Split(c.Value, " ")(0)
stringArray(1) = Split(c.Value, " ")(1)
stringArray(2) = Split(c.Value, " ")(2)
[/VBA]


What are you trying to do? It sort of appears like you want to delete rows if col D doesn't contain AMP Life Ltd. ???? But I didn't trace all the way though

Paul

[Giri]

The parentheses seems to have worked. I did the same for the second loop too.

Also, I noticed that if I didn't select the ActiveCell, certain rows would not be deleted.

Thanks Paul!

Kind Regards,

Giri

[Paul_Hossler]

When deleting rows, I've always found it's bettter to start at the bottom and delete and keep working my way to the top

That way any counters or other indicators (e.g. ActiveCell) don't get messed up and refer to a wrong row.

Paul

[Giri]

Thanks Paul - didn't think about that.

Kind Regards,

Giri