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

"Ayo" skrev i meddelelsen
...
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

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
--
HTH...

Jim Thomlinson


"Ayo" wrote:

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,

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" wrote:

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

Thanks Mike.

"Mike H" wrote:

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" wrote:

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

Thanks Jim. Works perfectly.

"Jim Thomlinson" wrote:

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
--
HTH...

Jim Thomlinson


"Ayo" wrote:

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

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





Ayo wrote:

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

--

Dave Peterson