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

Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Rob Henson

8/22/2022 - Mon
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.
Amir Azhdari

Or you might want to try
Selection.Rows(i).EntireRow.Delete

Open in new window


Rob Henson

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?
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
Justin Smith

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?
Amir Azhdari

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
Rob Henson

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Norie

You might want to add a sheet reference to Cells.
ASKER
Justin Smith

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
Rob Henson

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question