PDA

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



zebra4
10-24-2011, 10:45 AM
Hi,

I currently use the following code to remove empty rows :




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).EntireRow.Delete
End Sub



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
10-24-2011, 11:22 AM
You could add the names and ranges to an array eg

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

zebra4
10-24-2011, 02:02 PM
You could add the names and ranges to an array eg

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

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

Thank you very much.

Steven

zebra4
11-10-2011, 07:11 AM
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

For Each c In Sheets(arr(0, i)).Range(arr(1, i))
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.