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

  • Thread starter Thread starter Ayo
  • Start date Start date
A

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
 
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
 
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
 
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
 
Thanks Mike.

Mike H said:
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
Click to expand...
 
Thanks Jim. Works perfectly.

Jim Thomlinson said:
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
Click to expand...
 
If you delete the row while you're traversing the rows, it becomes a pain.

But you could build a range to delete in the loop and then delete it later:

dim c as range
dim delRng 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 delrng is nothing then
set delrng = c
else
set delrng = union(c, delrng)
end if
End If
Next c

if delrng is nothing then
delrng.entirerow.delete
end if
 
Back
Top