General programming help

[Erik]

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

[Don Guillett]

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
dguillett1@austin.rr.com
"Erik" <Erik@discussions.microsoft.com> wrote in message
news:153B91D8-BB20-46CC-8ED8-B40AAAA1CBA6@microsoft.com...
> 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

[Gary''s Student]

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

[Jim Thomlinson]

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

[Jim Thomlinson]

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

[Erik]

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.