I'd like to repurpose this code for excel 2003 - can anyone help?

[mfarah]

I get a mismatch when i try to run it in my 2003 file - any ideas why - it apprently works in excel 2007.

Sub Macro1()
Dim sheet As Worksheet
Dim usedRange As Range

Set sheet = ActiveSheet
Set usedRange = sheet.usedRange

Dim rowCount As Integer
Dim columnCount As Integer
Dim iRow As Integer
Dim iColumn As Integer

rowCount = usedRange.Rows.Count columnCount = usedRange.Columns.Count

For iRow = rowCount To 1 Step -1
For iColumn = 1 To columnCount

'this is where I get the problems
If ((InStr(1, LCase(usedRange(iRow, iColumn)), "overdue") > 0) Or (InStr(1, LCase(usedRange(iRow, iColumn)), "due") > 0)) Then usedRange.Range(Cells(iRow, 1), Cells(iRow, columnCount)).Delete
End If
Next iColumn
Next iRow

End Sub

 

[Domski]

Can't see anything that would cause problems in 2003 but not 2007. It's not a good idea to declare your variables with the same name as reserved VBA words like you have with UsedRange. Not sure if that could be the cause.

Dom

 

[StuLux]

I've checked the code in 2007 and it runs OK, have you tried changing the variable name from usedRange as suggested?

 

[mfarah]

I've checked the code in 2007 and it runs OK, have you tried changing the variable name from usedRange as suggested?

Ive change the variable name - (I think!)

If ((InStr(1, LCase(MyUsedRange(iRow, iColumn)), "overdue") > 0) Or (InStr(1, LCase(MyUsedRange(iRow, iColumn)), "due") > 0)) Then _
usedRange.Range(Cells(iRow, 1), Cells(iRow, columnCount)).delete
End If

now it seems not to recognise the If in the statement error message says "end IF without Block IF" - any ideas why?

 

[Domski]

You only need the End If if your If Then statement is split over multiple lines of code. Yours, is technically just one code line.

Try:

If ((InStr(1, LCase(MyUsedRange(iRow, iColumn)), "overdue") > 0) _
Or (InStr(1, LCase(MyUsedRange(iRow, iColumn)), "due") > 0)) Then _
Range(Cells(iRow, 1), Cells(iRow, columnCount)).Delete

Dom

 

[mfarah]

You only need the End If if your If Then statement is split over multiple lines of code. Yours, is technically just one code line.

Try:

If ((InStr(1, LCase(MyUsedRange(iRow, iColumn)), "overdue") > 0) _
Or (InStr(1, LCase(MyUsedRange(iRow, iColumn)), "due") > 0)) Then _
Range(Cells(iRow, 1), Cells(iRow, columnCount)).Delete

Dom

thanks - not sure if it's my instillation of excel but it now falls over at the dim statement here

Set MyUsedRange = sheet.usedRange