Do Loop

N

Noemi

Hi
I have a Do Loop which performs the task required however it does leave one
row left which it should also delete.

In one column I have a K and then numbers or Ktba. The Do Loop is to go down
the column to find and delete all the rows with Ktba in them and only stop
the loop when the column is empty.

The loop does work except it leaves a Ktba behind and I dont understand why.

IF you can see what is wrong with my code and advise me I would really
appreciate it.

count = 4

Do
If Application.Range("J" & count) = "Ktba" Then
Application.Range("J" & count).Select
Selection.EntireRow.Delete
End If

count = count + 1

Loop Until Application.Range("J" & count).value = ""

thanks
Noemi
 
D

Dave O

I was able to duplicate the problem- it occurs when the list ends with
two consecutive Ktba entries (in my mock up, at least). The problem
occurs because you augment the count by one, but the delete row action
effectively removes that count + 1 row from consideration.

I fixed the problem by decrementing count by 1 every time a row is
deleted- that causes the routine to back up a step and reconsider a row
that's already been treated, but fixes the skipped occurence problem.

Sub test()
Dim Count As Byte
Count = 4

Do
If Application.Range("J" & Count) = "Ktba" Then
Application.Range("J" & Count).Select
Selection.EntireRow.Delete
Count = Count - 1
End If

Count = Count + 1

Loop Until Application.Range("J" & Count).Value = ""
End Sub
 
K

Kleev

When you delete rows, you need to work backwards otherwise you end up
skipping rows (I tried your sub and entered sequential numbers in adjacent
rows and your routine was skipping every other row. The following routine
appears to work:

Sub ktba()
Dim StartRow As Long, FinalRow As Long, Count As Long
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("sheet1")
StartRow = 4
FinalRow = ws.Range("j" & Rows.Count).End(xlUp).Row

For Count = FinalRow To StartRow Step -1
If ws.Range("j" & Count) = "Ktba" Then
ws.Range("j" & Count).Select
Selection.EntireRow.Delete
End If

Next Count

End Sub
 
Top