For Loop
 

I'm assuming you're not using Excel 2007. The problem is most likely with
the lines

For i = lrow To 1 Step -1

and
s = Cells(j, i).Value

Here, if i is greater than 256, the maximum number of columns, the code will
blow up because i is greater than the number of columns. You can't reference
a cell past column 256.

For i = lrow To 1 Step -1

should probably be

For i = lcol To 1 Step -1

where lcol is the last column of data.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"hfazal" wrote in message
...
Hi, I run several macros on some data on a daily basis. Usually I have
about
a 100 rows of data, occasionally when I have about 500 the for loop in one
of
the macros crashes although it seems to perform the task (at least
partially
before it does). Below is the excerpt, hope someone can help why this
would
be:

Const Pends As String = "Pnd"
Dim lrow As Long
Dim s As String
Dim i, j As Long

For i = lrow To 1 Step -1
For j = lrow To 1 Step -1
s = Cells(j, i).Value
If Not (InStr(s, Pends) = 0) Then
Cells(j, i).EntireRow.Delete
End If
Next
Next

Would really appreciate some help. Thanks