PDA

View Full Version : Sub causing error 9



zebra4
11-11-2011, 07:27 AM
Hi,

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

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
The error occurs on the following line:

For Each c In Sheets(arr(0, i)).Range(arr(1, i))
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
11-11-2011, 07:40 AM
Does the 4th sheet exist?

zebra4
11-11-2011, 07:48 AM
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
11-11-2011, 08:24 AM
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
11-11-2011, 08:26 AM
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
11-11-2011, 08:34 AM
Is it the same error or different?

Try the following approach
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

zebra4
11-11-2011, 08:40 AM
Is it the same error or different?

Try the following approach
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


No, that doesn't work.

shrivallabha
11-11-2011, 08:46 AM
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
11-11-2011, 09:02 AM
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
11-15-2011, 06:40 AM
Are you saying the script runs OK despite the error? That is to say the error is there but code continues.

zebra4
11-16-2011, 12:36 AM
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
11-17-2011, 03:34 AM
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
11-17-2011, 06:59 AM
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. :thumb

Steven