Starting a For Loop from the last Row and going upwards.

[Ayo]

If I wanted this for loop to start from the bottom row, how do I go about
doing that. I want the row deletion to start from the last row going upwards.

For Each c In masterTrackerWs.Range("C2:C" & masterTrackerWs_lastRow).Cells
If c.Offset(0, 1).Text = "#N/A" And c.Offset(0, 2).Text = "#N/A" Then
c.EntireRow.Delete
End If
Next c

 

[Per Jessen]

HI

Try this:

For r = masterTrackersWS_lastRow To 2 Step -1
With masterTrackerWs.Range("C" & r)
If .Offset(0, 1).Text = "#N/A" And .Offset(0, 2).Text = "#N/A" Then
.EntireRow.Delete
End If
End With
Next

Regards,
Per

 

[Jim Thomlinson]

You can't with your existing code. Even if you could it will still cause
problems as the range you are traversing thorough is changing each time you
delete. On top of that deletes are slow and you are better off to accumulate
a single large range to delete. I would do it this way...

dim rngAll as range

For Each c In masterTrackerWs.Range("C2:C" & masterTrackerWs_lastRow).Cells
If c.Offset(0, 1).Text = "#N/A" And c.Offset(0, 2).Text = "#N/A" Then
if rngall is nothing then
set rngall = c
else
set rngall = union(rngall, c)
End If
Next c

if not rngall is nothing then rngall.entirerow.delete

 

[Mike H]

Hi,

This is how to do it backwards

For x = masterTrackerWs_lastRow To 2 Step -1
If Cells(x, 4).Text = "#N/A" And Cells(x, 5).Text = "#N/A" Then
Rows(x).EntireRow.Delete
End If
Next

but you don't have to, using your method. Build up a new range of relevent
cells and delete all in one go

Sub subtest1()
Dim copyrange As Range
Set masterTrackerWs = ActiveSheet
masterTrackerWs_lastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
For Each c In masterTrackerWs.Range("C2:C" & masterTrackerWs_lastRow).Cells
If c.Offset(0, 1).Text = "#N/A" And c.Offset(0, 2).Text = "#N/A" Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next c
If Not copyrange Is Nothing Then
copyrange.Delete
End If




End Sub

Mike

 

[Ayo]

Thanks Mike.

Hi,

This is how to do it backwards

For x = masterTrackerWs_lastRow To 2 Step -1
If Cells(x, 4).Text = "#N/A" And Cells(x, 5).Text = "#N/A" Then
Rows(x).EntireRow.Delete
End If
Next

but you don't have to, using your method. Build up a new range of relevent
cells and delete all in one go

Sub subtest1()
Dim copyrange As Range
Set masterTrackerWs = ActiveSheet
masterTrackerWs_lastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
For Each c In masterTrackerWs.Range("C2:C" & masterTrackerWs_lastRow).Cells
If c.Offset(0, 1).Text = "#N/A" And c.Offset(0, 2).Text = "#N/A" Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next c
If Not copyrange Is Nothing Then
copyrange.Delete
End If




End Sub

Mike

 

[Ayo]

Thanks Jim. Works perfectly.

You can't with your existing code. Even if you could it will still cause
problems as the range you are traversing thorough is changing each time you
delete. On top of that deletes are slow and you are better off to accumulate
a single large range to delete. I would do it this way...

dim rngAll as range

For Each c In masterTrackerWs.Range("C2:C" & masterTrackerWs_lastRow).Cells
If c.Offset(0, 1).Text = "#N/A" And c.Offset(0, 2).Text = "#N/A" Then
if rngall is nothing then
set rngall = c
else
set rngall = union(rngall, c)
End If
Next c

if not rngall is nothing then rngall.entirerow.delete