Solved: Removing Empty Rows on Multiple Sheets At The Same Time

[zebra4]

Hi,

I currently use the following code to remove empty rows :


[vba]

Public Sub
RemoveEmptyRows()
Dim c As Range
For Each c In Sheets("FIN_PENS_TAB").Range("A1:A105")
If c = "" Then c.ClearContents
Next c Sheets("FIN_PENS_TAB").Range("A1:A105").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete
End Sub

[/vba]

I first delete the formula from the first cell if the cell is empty and then I delete the entire row if the first cell is empty.

This works just fine.

However, I would like to do this for multiple worksheets. The sheets all have different names (not sheet1, sheet2, etc) and the ranges are not always the same (I don't want to apply it to the entire worksheet, only to a specific range).

I've tried using union but that doesn't work.

Any suggestions on how to do this?

Thanks in advance.

Steven

[mdmackillop]

You could add the names and ranges to an array eg

[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.ClearContents
Next
End Sub
[/VBA]

[zebra4]

You could add the names and ranges to an array eg

[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.ClearContents
Next
End Sub
[/vba]

Great! Exactly what I was looking for. Only one adjustment needed: Clearcontents needs to be replaced by delete.

Thank you very much.

Steven

[zebra4]

Great! Exactly what I was looking for. Only one adjustment needed: Clearcontents needs to be replaced by delete.

Thank you very much.

Steven

I had tested the script with 2 sheets, I just tried it for 4 sheets like the script sample above and I'm getting an error 9 -subscript out of range- on the following line

[vba]For Each c In Sheets(arr(0, i)).Range(arr(1, i))[/vba]
The script did what it had to do for the first 3 sheets, not for the 4th.

Any ideas why I'm getting an error 9?

Thanks in advance.