For Next Loop Problem

Posted by JAF on June 23, 2000 12:14 AM

I have a macro that goes through a column of data and replaces duplicate entries with the text string ---

I'm trying to create a For/Next Loop that will go through the data and for each cell where the value is --- to delete that row and move on to the next row.

The code I have is as follows:
Sub test_for_next_loop()
For Each Cell In Selection
If Cell.Value = "-----" Then
Cell.EntireRow.Delete Shift:=xlUp
End If
Next Cell
End Sub

This works (to an extent), but where I have consecutive instances of ---, it only replaces the first one. eg.
Client 1
---
---
Client 2
---
---
---
Client 3
---

becomes...
Client 1
---
Client 2
---
---
Client 3

What I want is...
Client 1
Client 2
Client 3

Any suggestions as to what I'm doing wrong?

Posted by Ryan on June 23, 0100 3:34 AM

Here is some code that will take care of what you need. Hope you like it, let me know.

Ryan

Sub SearchAndDelete()
Dim Row As Integer

On Error GoTo ErrHandler

Application.ScreenUpdating = False

Do
Row = Cells.Find(What:="---", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False).Row
Rows(Row).Delete Shift:=xlUp
Loop

ErrHandler:
Application.ScreenUpdating = True
Exit Sub

End Sub

Posted by mads on June 23, 0100 4:52 AM

This is a nice solution but it will delete all "---" from the worksheet.
If it is not required to delete all "---", then "Selection" should be used in the macro instead of "Cells".
Also, even if "Selection" is used, deletions outside of the selection might be made. For example, if the selection is A1:A10 and rows are deleted, rows with "---" will continue to be be deleted until the REMAINING rows A1:A10 do not contain "---".
A different way to avoid rows being "skipped" when deleting rows is to loop from the last row to the first row. Another way is to loop thru the selection and store the rows to delete and then delete them all at once. For example :-

Sub test_for_next_loop()
Dim ToDelete As Range, Cell As Range
For Each Cell In Selection
If Cell.Value = "-----" Then
On Error GoTo ErrorHandler
Set ToDelete = Application.Union(ToDelete, Cell.EntireRow)
End If
Next
On Error GoTo EndIt
ToDelete.Delete
ErrorHandler:
Set ToDelete = Cell.EntireRow
Resume Next
EndIt:
Exit Sub
End Sub

mads