Sub causing error 9

[zebra4]

Hi,

I'm getting an error 9 (subscript out of range) when running the script below:

[vba]Public Sub RemoveEmptyRows()
Dim c As Range
Dim i As Long
Dim arr(1, 3)

arr(0, 0) = "Sheet1"
arr(1, 0) = "A1:A105"
arr(0, 1) = "Sheet2"
arr(1, 1) = "A1:A100"
arr(0, 2) = "Sheet3"
arr(1, 2) = "A1:A95"
arr(0, 3) = "Sheet4"
arr(1, 3) = "A1:A90"

For i = 0 To 3
For Each c In Sheets(arr(0, i)).Range(arr(1, i))
If c = "" Then c.ClearContents
Next c
Sheets(arr(0, i)).Range(arr(1, i)).SpecialCells(xlCellTypeBlanks).EntireRow.EntireRow.Delete
Next
End Sub [/vba]
The error occurs on the following line:

[vba]For Each c In Sheets(arr(0, i)).Range(arr(1, i))[/vba]
The script: step 1) remove formula from empty cell withing range, step 2) delete entire row if first cell is empty

It works for the first 3 sheets, not for the last.

Any ideas why I'm getting an error 9?

Thanks in advance.

[shrivallabha]

Does the 4th sheet exist?

[zebra4]

Does the 4th sheet exist?

Yes, but I discovered my mistake. There was a typo in the sheet's name (blank space instead of underscore).

Now everything works. Hoewever, I'm still getting the error.

Thanks.
Steven

[shrivallabha]

Yes, but I discovered my mistake. There was a typo in the sheet's name (blank space instead of underscore).

Now everything works. Hoewever, I'm still getting the error.

Thanks.
Steven

Where does it occur? And when?

[zebra4]

Where does it occur? And when?

The error occurs on the following line:

VBA:

For Each c In Sheets(arr(0, i)).Range(arr(1, i))

I run the script through a button. It occurs while running.

[shrivallabha]

Is it the same error or different?

Try the following approach
[VBA]Public Sub RemoveEmptyRows()
Dim c As Range
Dim i As Long
Dim arr(1, 3)

arr(0, 0) = 1 'Changed here if it works then code can be ammended further.
arr(1, 0) = "A1:A105"
arr(0, 1) = 2
arr(1, 1) = "A1:A100"
arr(0, 2) = 3
arr(1, 2) = "A1:A95"
arr(0, 3) = 4
arr(1, 3) = "A1:A90"

For i = 0 To 3
For Each c In Sheets(arr(0, i)).Range(arr(1, i))
If c = "" Then c.ClearContents
Next c
Sheets(arr(0, i)).Range(arr(1, i)).SpecialCells(xlCellTypeBlanks).EntireRow.EntireRow.Delete
Next
End Sub
[/VBA]

[zebra4]

Is it the same error or different?

Try the following approach
[vba]Public Sub RemoveEmptyRows()
Dim c As Range
Dim i As Long
Dim arr(1, 3)

arr(0, 0) = 1 'Changed here if it works then code can be ammended further.
arr(1, 0) = "A1:A105"
arr(0, 1) = 2
arr(1, 1) = "A1:A100"
arr(0, 2) = 3
arr(1, 2) = "A1:A95"
arr(0, 3) = 4
arr(1, 3) = "A1:A90"

For i = 0 To 3
For Each c In Sheets(arr(0, i)).Range(arr(1, i))
If c = "" Then c.ClearContents
Next c
Sheets(arr(0, i)).Range(arr(1, i)).SpecialCells(xlCellTypeBlanks).EntireRow.EntireRow.Delete
Next
End Sub
[/vba]

No, that doesn't work.

[shrivallabha]

Can you upload the workbook (sample data) please?. When you are replying to the post, there's an option "Manage Attachments". Use it to upload a sample file.

[zebra4]

Can you upload the workbook (sample data) please?. When you are replying to the post, there's an option "Manage Attachments". Use it to upload a sample file.

Thanks. But, I can't upload the workbook (private, financial data and too much work to create a new one just to solve this). The script does what I want it to do, so the error isn't that much of an issue. Thanks for your help.

[IanFScott]

Are you saying the script runs OK despite the error? That is to say the error is there but code continues.

[zebra4]

Are you saying the script runs OK despite the error? That is to say the error is there but code continues.

Yes, The script runs, does what it has to do, but I still get the error.

[IanFScott]

Forgot to ask - are you using Office 2007?
If you are then I have the same problem - any vba change to a worksheet throws the error but does NOT stop code running. The work around (as long as you do not need events to fire) is to put:
Application.EnableEvents = False
before the code which changes the worksheet then
Application.EnableEvents = True
after the code.
The error is thrown any time the Worksheet_Change event occurs.
Otherwise you have to adjust code to cope with the fact that Err.Number is not zero if you need to check for errors later in the code.

[zebra4]

Forgot to ask - are you using Office 2007?
If you are then I have the same problem - any vba change to a worksheet throws the error but does NOT stop code running. The work around (as long as you do not need events to fire) is to put:
Application.EnableEvents = False
before the code which changes the worksheet then
Application.EnableEvents = True
after the code.
The error is thrown any time the Worksheet_Change event occurs.
Otherwise you have to adjust code to cope with the fact that Err.Number is not zero if you need to check for errors later in the code.

Yes!

Thanks, your solution works perfectly.

Steven