Help with rows delete macro

Microsoft Office 2007 home and student
September 29, 2009 at 03:30:47
Specs: Windows XP
Can someone help me look at what is wrong
with my code below? It only delete what I
wanted to delete in the first sheet. I have 10
sheets for it to thourgh at.

DataStartRow = 51
SearchColumn = "O"

SearchItems = Split("delete, DELETE", ",")

On Error GoTo Whoops
OriginalCalculationMode =
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

For Each working_sheets In Worksheets
With working_sheets
LastRow = .Cells(.Rows.Count,
For X = LastRow To DataStartRow Step -1
FoundRowToDelete = False
For Z = 0 To UBound(SearchItems)
If InStr(.Cells(X, SearchColumn).Value,
SearchItems(Z)) Then
FoundRowToDelete = True
Exit For
End If


If FoundRowToDelete Then
If RowsToDelete Is Nothing Then
Set RowsToDelete = .Cells(X, SearchColumn)
Set RowsToDelete = Union(RowsToDelete,
.Cells(X, SearchColumn))
End If

If RowsToDelete.Areas.Count > 100 Then
Set RowsToDelete = Nothing
End If
End If


End With
If Not RowsToDelete Is Nothing Then
End If
Next working_sheets

Application.Calculation =
Application.ScreenUpdating = True

See More: Help with rows delete macro

Report •

September 29, 2009 at 05:42:14
Without dissecting your code, I'm going to ask a question:

Have you used F8 and the Watch Window to single step through the code to see what it is doing?

Many times, using F8 while watching your variables in the Watch Window will allow you see why it is acting the way it is.

For example...

Is it really looping through all sheets or is it simply going through the loop 10 times but always working on the ActiveSheet?

Are row counter variables being reset for each time through the loop or are they retaining "old" values.


F8 and the Watch Window are very powerful troubleshooting tools.

Report •

September 29, 2009 at 05:59:37

I noticed two things:
1. Split - remove the space between delete, and DELETE it should be delete,DELETE
otherwise the uppecase is " DELETE" instead iof "DELETE"

2.After the first sheet an error occurs at
Set RowsToDelete = Union(RowsToDelete, .Cells(X, SearchColumn))

So this is the line that needs to be worked on.

I just used some breakpoints and a combination of f5 and f8 to find this.

Also it is good practice and helps avoid errors to use Option Explicit at the top of your module and define each variable with a Dim XYZ AS statement.

For posting code, please use the pre tags (above the message box) and put your code between the PRE and /PRE tags.
This maintains indenting and makes the code a lot easier to read.


Report •

September 30, 2009 at 05:57:32
I see...thanks for the ideas....
because after following it, it seem to stop after discover a
"delete" and delete that row.

Report •

Related Solutions

Ask Question