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

mfarah

New Member
Joined
Aug 20, 2008
Messages
35
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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

Well-known Member
Joined
Sep 14, 2005
Messages
643
Office Version
  1. 365
Platform
  1. Windows
I've checked the code in 2007 and it runs OK, have you tried changing the variable name from usedRange as suggested?
 

mfarah

New Member
Joined
Aug 20, 2008
Messages
35
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

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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:

Code:
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

New Member
Joined
Aug 20, 2008
Messages
35
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:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,481
Messages
5,596,391
Members
414,063
Latest member
N_Bates

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top