Delete All Blank Rows--A plea for help

[Elessvie]

Hello, kind folks. Excel 2007 here. For the life of me I've been unable to
figure out why none of the 3 macros I have to delete blank rows is not
working. All of the macros are from this wonderful discussion group, and
they have ALWAYS worked with a little tweaking.

The spreadsheet I am trying to work on is a straight-ahead grid of 25,795
rows and columns A-N. Some cells are text, some are numbers, and I've set
them all to "Text." What happens when any of the macros run is that all of
the rows are deleted. I've tried various formats on the cells, but the
result is always the same.

Below are the macros I've been using. Could anybody please tell me what I
am doing wrong? Thank you for your time. -Lynne


MACRO ONE==========================================

Sub RemoveBlankRows()
Dim rg As Range, rgBlank As Range
'-------- CHANGE HERE -----------
Set rg = ActiveSheet.Range("A1:N25797")
'--------------------------------

'get blank cells from rg
On Error Resume Next
Set rgBlank = rg.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rgBlank Is Nothing Then 'no blank cell
MsgBox "No Blank cells found"
Else 'else delete entire rows
rgBlank.EntireRow.Delete
End If
End Sub

MACRO TWO: ===========================================

Sub RemoveBlankRows2()
On Error Resume Next
Columns(1).SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

MACRO THREE: ==========================================

Sub RemoveBlankRows3()
On Error Resume Next
Range("A9:A25797").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

 

[Bernie Deitrick]

Elessvie,

All three of your macros worked fine for me. Perhaps you have a hidden column that is entirely
blank?

Instead of using code, try just sorting your entire sheet based on one column - the blanks should go
to the bottom.

HTH,
Bernie
MS Excel MVP

 

[Elessvie]

It must the large number of rows. I've tried the macros on a sheet limited
to 10,000 rows and it works fine.

Does anyone know the spreadsheet size limit for macros like this?

Once again, thank you for your time.

-Lynne

 

[Elessvie]

Thank you, Bernie. Sorting is what I ended up having to do, and that was OK.
I really needed to understand what the problem with the macros themselves
was, though. I think I found the problem, and that is that that the
spreadsheet had more rows than they could handle. I've been trying different
numbers of rows and so far have found that 10,000 rows is fine, but 20,000 is
not. I'm still working on finding the limit.

Thank you kindly once again,
-Lynne

 

[Bernie Deitrick]

I think the limit is something like 8,192 areas. The sorting approach doesn't have that limitation.

Bernie
MS Excel MVP

 

[Jim Cone]

Try adding this code line just below the "goto 0" line...

MsgBox rgBlank.Areas.Count

If the count is > 8192 then the entire range is returned and everything gets deleted.
http://support.microsoft.com/default.aspx?scid=kb;en-us;832293
--
Jim Cone
Portland, Oregon USA




"Elessvie"
<[email protected]>
wrote in message
Hello, kind folks. Excel 2007 here. For the life of me I've been unable to
figure out why none of the 3 macros I have to delete blank rows is not
working. All of the macros are from this wonderful discussion group, and
they have ALWAYS worked with a little tweaking.

The spreadsheet I am trying to work on is a straight-ahead grid of 25,795
rows and columns A-N. Some cells are text, some are numbers, and I've set
them all to "Text." What happens when any of the macros run is that all of
the rows are deleted. I've tried various formats on the cells, but the
result is always the same.

Below are the macros I've been using. Could anybody please tell me what I
am doing wrong? Thank you for your time. -Lynne


MACRO ONE==========================================

Sub RemoveBlankRows()
Dim rg As Range, rgBlank As Range
'-------- CHANGE HERE -----------
Set rg = ActiveSheet.Range("A1:N25797")
'--------------------------------

'get blank cells from rg
On Error Resume Next
Set rgBlank = rg.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rgBlank Is Nothing Then 'no blank cell
MsgBox "No Blank cells found"
Else 'else delete entire rows
rgBlank.EntireRow.Delete
End If
End Sub

MACRO TWO: ===========================================

Sub RemoveBlankRows2()
On Error Resume Next
Columns(1).SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

MACRO THREE: ==========================================

Sub RemoveBlankRows3()
On Error Resume Next
Range("A9:A25797").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

 

[Elessvie]

Thank you very much! I will try this.

-Lynne