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,

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" <(E-Mail Removed)> wrote in message
news:0C98C381-E502-42B8-B376-(E-Mail Removed)...
> 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
>
>
>

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" wrote:

> 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
>
>
>

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:0C98C381-E502-42B8-B376-(E-Mail Removed)...
> > 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
> >
> >
> >
>
>
>

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

Bernie
MS Excel MVP


"Elessvie" <(E-Mail Removed)> wrote in message
news:6FDB30F3-53DA-464B-88B3-(E-Mail Removed)...
> 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" wrote:
>
>> 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
>>
>>
>>

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...b;en-us;832293
--
Jim Cone
Portland, Oregon USA




"Elessvie"
<(E-Mail Removed)>
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

Thank you very much! I will try this.

-Lynne

"Jim Cone" wrote:

> 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...b;en-us;832293
> --
> Jim Cone
> Portland, Oregon USA
>
>
>
>
> "Elessvie"
> <(E-Mail Removed)>
> 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
>
>
>
>