I'm trying to use the code below to delete rows from a sheet. The problem is
that as it deletes a row, it then skips the next one. How can I create a
list of the rows to be deleted and delete them all at once after the For loop?

Set ColA = Intersect(Sheet1.Columns("A"), Sheet1.UsedRange)
For Each cell In ColA
If cell.Value = Empty And Not cell.Offset(0, 1).Value = "Weekend Diff"
Then
cell.EntireRow.Delete shift:=xlUp
End If
Next cell

the trick is to work from the bottom up
for i=cells(rows.count,"a").end(xlup).row to 2 step -1
if cells(i,"a") etc
next i

--
Don Guillett
SalesAid Software

"Erik" wrote in message
...
I'm trying to use the code below to delete rows from a sheet. The problem
is
that as it deletes a row, it then skips the next one. How can I create a
list of the rows to be deleted and delete them all at once after the For
loop?

Set ColA = Intersect(Sheet1.Columns("A"), Sheet1.UsedRange)
For Each cell In ColA
If cell.Value = Empty And Not cell.Offset(0, 1).Value = "Weekend
Diff"
Then
cell.EntireRow.Delete shift:=xlUp
End If
Next cell

You need to work from the bottom of UsedRange back up to the top.
--
Gary''s Student


"Erik" wrote:

I'm trying to use the code below to delete rows from a sheet. The problem is
that as it deletes a row, it then skips the next one. How can I create a
list of the rows to be deleted and delete them all at once after the For loop?

Set ColA = Intersect(Sheet1.Columns("A"), Sheet1.UsedRange)
For Each cell In ColA
If cell.Value = Empty And Not cell.Offset(0, 1).Value = "Weekend Diff"
Then
cell.EntireRow.Delete shift:=xlUp
End If
Next cell

Create a new range object to accumulate the cells that you find. Note that
the union will fail unless rngToDelete is something other than nothing, so
you need to check this each time through the loop, but it will only execute
once. If you are looking to improve the efficiency you might want to consider
changing the used range of column A to the blank cells in column A. Depending
how much data you have and how it looks this could make for a huge
improvement in the speed and efficiency...

Dim rngToDelete As Range

Set ColA = Intersect(Sheet1.Columns("A"), Sheet1.UsedRange)
For Each cell In ColA
If cell.Value = Empty And Not cell.Offset(0, 1).Value = "Weekend Diff"
Then
If rngToDelete Is Nothing Then Set rngToDelete = cell
Set rngToDelete = Union(rngToDelete, cell)
End If
Next cell
rngToDelete.EntireRow.Delete shift:=xlUp

To improve efficiency

Dim rngToDelete As Range
Dim colA As Range
On Error Resume Next
Set colA = Sheet1.Columns("A").SpecialCells(xlBlanks)
On error goto 0
If colA Is Nothing Then Exit Sub

For Each cell In colA
If Not cell.Offset(0, 1).Value = "Weekend Diff" Then
If rngToDelete Is Nothing Then Set rngToDelete = cell
Set rngToDelete = Union(rngToDelete, cell)
End If
Next cell
rngToDelete.EntireRow.Delete shift:=xlUp
--
HTH...

Jim Thomlinson


"Erik" wrote:

I'm trying to use the code below to delete rows from a sheet. The problem is
that as it deletes a row, it then skips the next one. How can I create a
list of the rows to be deleted and delete them all at once after the For loop?

Set ColA = Intersect(Sheet1.Columns("A"), Sheet1.UsedRange)
For Each cell In ColA
If cell.Value = Empty And Not cell.Offset(0, 1).Value = "Weekend Diff"
Then
cell.EntireRow.Delete shift:=xlUp
End If
Next cell

Just a little clarification. Gary and Don's code is great and if the number
of deletes to be performed is small then based on the simplicity of the code
it is probably the way to go. If there are a lot of deletes to be done, then
accumulating all of the rows and then doing one big delete will be faster. So
the code that you end up using should depend on the number of rows to
delete...
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

Create a new range object to accumulate the cells that you find. Note that
the union will fail unless rngToDelete is something other than nothing, so
you need to check this each time through the loop, but it will only execute
once. If you are looking to improve the efficiency you might want to consider
changing the used range of column A to the blank cells in column A. Depending
how much data you have and how it looks this could make for a huge
improvement in the speed and efficiency...

Dim rngToDelete As Range

Set ColA = Intersect(Sheet1.Columns("A"), Sheet1.UsedRange)
For Each cell In ColA
If cell.Value = Empty And Not cell.Offset(0, 1).Value = "Weekend Diff"
Then
If rngToDelete Is Nothing Then Set rngToDelete = cell
Set rngToDelete = Union(rngToDelete, cell)
End If
Next cell
rngToDelete.EntireRow.Delete shift:=xlUp

To improve efficiency

Dim rngToDelete As Range
Dim colA As Range
On Error Resume Next
Set colA = Sheet1.Columns("A").SpecialCells(xlBlanks)
On error goto 0
If colA Is Nothing Then Exit Sub

For Each cell In colA
If Not cell.Offset(0, 1).Value = "Weekend Diff" Then
If rngToDelete Is Nothing Then Set rngToDelete = cell
Set rngToDelete = Union(rngToDelete, cell)
End If
Next cell
rngToDelete.EntireRow.Delete shift:=xlUp
--
HTH...

Jim Thomlinson


"Erik" wrote:

I'm trying to use the code below to delete rows from a sheet. The problem is
that as it deletes a row, it then skips the next one. How can I create a
list of the rows to be deleted and delete them all at once after the For loop?

Set ColA = Intersect(Sheet1.Columns("A"), Sheet1.UsedRange)
For Each cell In ColA
If cell.Value = Empty And Not cell.Offset(0, 1).Value = "Weekend Diff"
Then
cell.EntireRow.Delete shift:=xlUp
End If
Next cell

Thanks guys. The working from bottom up was pretty obvious. I should have
seen that myself..just not thinking. I do have over 1000 lines to delete on
a regular basis, so I think I'm going to go with collecting the rows and
deleting all at once. Thanks again.