Link to home
Start Free TrialLog in
Avatar of Justin Smith
Justin SmithFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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

Avatar of Amir Azhdari
Amir Azhdari
Flag of United States of America image

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

Open in new window


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

Open in new window

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?
Avatar of Justin Smith

ASKER

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?
Avatar of Norie
Norie

You might want to add a sheet reference to Cells.
HI Rob, what would be a good Loop going up, instead of down?

Starting at Row 200 and going up to row 1
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial