# Problem with Do Loop

Hi Guys, I have a Do Loop here with an If Statement and its deleting all the rows. Whats wrong with the code?

Sub OGL ()
Sheets ("OGL").Select
dim i as integer
i = 5

Do Until Cells (i,1) = Cells (200,1)

i = i + 1
If Cells (i,10).Value = "641025" & Cells (1,11).Value = "GAIN/LOSS ON EXCHANGE" Then Cells (i,10).Select
Selection.EntireRow.Delete
i = i +1
Loop

Amir Azhdari

You need to put "End If" after Selection.EntireRow.Delete (OR i =i+1 up to your needs) otherwise IF condition only applies to Cells (i,10).Select when it's true.
Or you might want to try
``Selection.Rows(i).EntireRow.Delete``

As per discussion in your other question,:
Dim i as Long
Would be better as Integer is limited to c37000

Once again you are working down the sheet to delete rows, it is better to work up.

The value (if any) of Cells(200,1) (A200) will change with each iteration because A200 will become a new cell each time, existing A200 will move up when rows above are deleted and A201 will replace it.

You are increasing i by 2 each time, the i+1 is repeated.

As Amir has pointed out the If Statement doesn't have an End If. It is possible to have an If statement without End If when there is only one command required in the If, in your case the cell is getting selected as part of the one line If, then whatever is selected is getting deleted even if it doesn't meet the If criteria; hence why all rows are being deleted. You also need to change the & for AND otherwise the comparison is concatenating 641025 with "GAIN/LOSS ON EXCHANGE".

You could achieve the one line If like this:
``If Cells (i,10).Value = "641025" AND Cells (1,11).Value = "GAIN/LOSS ON EXCHANGE" Then Cells (i,10).EntireRow.Delete``
Why are you comparing the contents of column J each iteration with the contents of K1?

Can you explain what you are trying to achieve? Is this a surplus rows issue as well, like your other question?

Hi Rob, the content of K1 was a typo as I am typing the code onto it from my work computer. Due to firewalls, I cannot just copy from my work file to this website. I am getting the "End If without Block if" error message. How do I fix this?
Are you trying something like the following?

Sub OGL ()
Sheets ("OGL").Select
dim i as integer
i = 5

Do Until Cells (i,1) = Cells (200,1)

If Cells (i,10).Value = "641025" & Cells (1,11).Value = "GAIN/LOSS ON EXCHANGE" Then
Cells (i,10).Select
Selection.EntireRow.Delete
End If
i = i + 1
Loop
Amir's comment above should fix the "End If without Block if" error message. Still need to change the & for AND.

Can you explain what you are trying to achieve? Is this a surplus rows issue as well, like your other question?
Norie

You might want to add a sheet reference to Cells.