Is it possible to reset the starting cell in a For Loop

[Ayo]

I have the following snipet of code. What I am trying to do is delete ranges
of consecutive rows from the worksheet. The problem is that there are gaps
between these rows of about 12 rows of other data that needs to be left
alone.
What I am trying to find out is, is there a way to reset the starting cell
in "For Each c1 In ws.Range("A4:A" & ClastRow).Cells each time I delete the
needed rows? I tried "For Each c1 In ws.Range("A" & stratingRow & ":A" &
ClastRow).Cells, but that didn't seem to work exactly as i hoped.
Any help will be greatly appreaciated.

For Each ws In Worksheets
If Mid(ws.Name, 1, 2) = "B0" Then
ws.Select
ClastRow = ActiveSheet.UsedRange.Rows.Count
currentRow = 4
startingRow = 4
For Each c1 In ws.Range("A4:A" & ClastRow).Cells
c1.Select
If Left(c1, 2) = "NY" Or Left(c1, 2) = "07" Or Left(c1, 2) =
"55" Then
currentRow = currentRow + 1
ElseIf c1 <> "" And c1 = "TOTAL" Then
EndingRow = currentRow - 1
ws.Rows(startingRow & ":" & EndingRow).Select
Selection.ClearContents
ws.Rows(startingRow & ":" & EndingRow - 1).Select
Selection.Delete Shift:=xlUp
currentRow = startingRow + 12
startingRow = currentRow
Else
End If
Next c1
End If
Next ws

 

[Ron Rosenfeld]

I have the following snipet of code. What I am trying to do is delete ranges
of consecutive rows from the worksheet. The problem is that there are gaps
between these rows of about 12 rows of other data that needs to be left
alone.
What I am trying to find out is, is there a way to reset the starting cell
in "For Each c1 In ws.Range("A4:A" & ClastRow).Cells each time I delete the
needed rows? I tried "For Each c1 In ws.Range("A" & stratingRow & ":A" &
ClastRow).Cells, but that didn't seem to work exactly as i hoped.
Any help will be greatly appreaciated.

For Each ws In Worksheets
If Mid(ws.Name, 1, 2) = "B0" Then
ws.Select
ClastRow = ActiveSheet.UsedRange.Rows.Count
currentRow = 4
startingRow = 4
For Each c1 In ws.Range("A4:A" & ClastRow).Cells
c1.Select
If Left(c1, 2) = "NY" Or Left(c1, 2) = "07" Or Left(c1, 2) =
"55" Then
currentRow = currentRow + 1
ElseIf c1 <> "" And c1 = "TOTAL" Then
EndingRow = currentRow - 1
ws.Rows(startingRow & ":" & EndingRow).Select
Selection.ClearContents
ws.Rows(startingRow & ":" & EndingRow - 1).Select
Selection.Delete Shift:=xlUp
currentRow = startingRow + 12
startingRow = currentRow
Else
End If
Next c1
End If
Next ws

When deleting rows, because of the shifting up that occurs as you delete each
for, it is generally better to work from the bottom up.

So your algorithm might go something like:

Go to the bottom cell in the column.
Move up to first cell with data and get row number

LastRow = Range("A65535").end(xlup).row

Then test
delete if necessary
go to next row up
repeat

For i = LastRow to FirstRow Step -1
If "Test if needs to be deleted" = true then
cells(i,1).entirerow.delete
end if
next i

etc.

--ron

 

[JE McGimpsey]

If I understand you correctly, one way:

Const cnSTART_ROW As Long = 4

Dim ws As Worksheet
Dim rDelete As Range
Dim i As Long
Dim nEnd As Long
Dim nLastRow As Long
Dim nStart As Long
Dim sTemp As String

For Each ws In ActiveWorkbook.Worksheets
With ws
If .Name Like "BO*" Then
nStart = cnSTART_ROW
nEnd = nStart - 1
nLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
i = cnSTART_ROW
Do While i <= nLastRow
sTemp = UCase(.Cells(i, 1).Text)
If Len(sTemp) = 0 Then
i = i + 1
Else
If sTemp = "TOTAL" Then
If nEnd >= nStart Then
If rDelete Is Nothing Then
Set rDelete = .Range( _
nStart & ":" & nEnd)
Else
Set rDelete = Union(rDelete, _
.Range(nStart & ":" & nEnd))
End If
End If
i = i + 12
nStart = i
Else
sTemp = Left(sTemp, 2)
If sTemp = "NY" Or sTemp = "O7" Or _
sTemp = "55" Then nEnd = i
i = i + 1
End If
End If
Loop
End If
End With
If Not rDelete Is Nothing Then
rDelete.EntireRow.Delete
Set rDelete = Nothing
End If
Next ws